Error using OLD and NEW records

From: Germán Hüttemann Arza <ghuttemann(at)cnc(dot)una(dot)py>
To: pgsql-general(at)postgresql(dot)org
Subject: Error using OLD and NEW records
Date: 2006-07-25 14:04:44
Message-ID: 200607251004.44928.ghuttemann@cnc.una.py
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, I'm writting because I get an error when I try to use OLD and NEW records
in a trigger procedure. The exact error messages were:

NEW used in quere that is not in a rule
PL/pgSQL function "audit_persona" line 6 at SQL statement

OLD used in quere that is not in a rule
PL/pgSQL function "audit_persona" line 3 at SQL statement

I receive them when the function is executed. It is called by a trigger:

CREATE TRIGGER audit_persona_all
AFTER INSERT OR DELETE OR UPDATE
ON persona
FOR EACH ROW
EXECUTE PROCEDURE audit_persona();

My function "audit_persona" is the follow:

CREATE FUNCTION audit_persona() RETURNS TRIGGER AS
'BEGIN
IF (TG_OP = ''DELETE'') THEN
INSERT INTO audit_persona SELECT OLD.*, user, ''D'', now();
RETURN OLD;
ELSE
INSERT INTO audit_persona SELECT NEW.*, user, substr(TG_OP, 1, 1), now();
RETURN NEW;
END IF;
RETURN NULL;
END;'
LANGUAGE plpgsql;

It is very similar to the example for auditing a table shown in the
documentation of the pgAdminIII, but it doesn't work at all.

Could you help me with this problem?

Thanks,

--
Germán Hüttemann Arza
CNC - Centro Nacional de Computación
UNA - Universidad Nacional de Asunción
Campus Universitario, San Lorenzo - Paraguay
http://www.cnc.una.py  - Tel.: 595 21 585550

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2006-07-25 14:07:37 Re: Archiving wal files
Previous Message Wes 2006-07-25 13:55:55 Re: Lock changes with 8.1 - what's the right lock?