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
