I'm trying to create a trigger (AFTER INSERT, UPDATE, DELETE) as an audit routine inserting into an audit table the "before" and "after" views of the row being acted upon. My problem is I defined the "before" and "after" fields in the audit table as TEXT and when I try to move NEW or OLD into these fields I get the error "NEW used in query that is not in a rule". I tried defining a variable as RECORD type but when I tried executing I would get a "syntax error at or near..." the variable when it was referenced in the code (new_fld := NEW for instance).
I'm currently stumped. I don't want an audit table for each and every table I want to audit. I want a single audit table to handle multiple tables. Do any of you more astute users have any ideas to help me? Can you tell me where I'm going wrong? Is my wish to have a single audit table for multiple tables all folly? An explanation of the "rule" error shown above would help as well.
Any help will be appreciated.
TIA,
Duane
Here is the function definition:
CREATE OR REPLACE FUNCTION func_aud_tst01() RETURNS trigger AS '
DECLARE
action char(1);
b4 text;
aftr text;
BEGIN
IF TG_OP = ''INSERT'' THEN
action := ''I'';
b4 := '''';
aftr := NEW;
-- b4 := ''Test b4 I'';
-- aftr := ''Test aftr I'';
ELSIF TG_OP = ''UPDATE'' THEN
action := ''U'';
-- b4 := OLD;
-- aftr := NEW;
b4 := ''Test b4 U'';
aftr := ''Test aftr U'';
ELSE
action := ''D'';
-- b4 := OLD;
-- aftr := '''';
b4 := ''Test b4 D'';
aftr := ''Test aftr D'';
END IF;
insert into audtst(table_name, act_type, before_look, after_look)
values(TG_RELNAME, action, b4, aftr);
RETURN NEW;
END;
' LANGUAGE plpgsql;
--
COMMIT WORK;