Thread: Error using OLD and NEW records

From

Germán Hüttemann Arza

Date:

26 July 2006, 14:47:26

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