Thread: Trigger & Function

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;

> 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".

You're trying to insert record data into a text field, that doesn't work.
OLD and NEW can be used as either record identifiers (as in RETURN OLD)
or column qualifiers (as in OLD.colname), but you can't intermingle them.

I don't think postgres (pl/pgsql) has row-to-variable and variable-to-row
functions like serialize and unserialize, that's probably what you'd need.
It would probably be necessary to write something like that in C, since
at this point pl/perl cannot be used for trigger functions.

I've not tried using pl/php yet, the announcement for it says it can be
used for trigger functions.

My first thought is that even if there was a serialize/unserialize
capabiity you might be able to write something using it that creates
the log entry but not anything that allows you to query the log for
specific column or row entries.

It would probably require a MAJOR extension of SQL to add it to pg,
as there would need to be qualifiers that can be mapped to specific
tables and columns.  Even if we had that, storing values coming from
multiple tables into a single audit table would present huge challenges.

I've found only two ways to implement audit logs:

1.  Have separate log tables that match the structure of
    the tables they are logging.

2.  Write a trigger function that converts columns to something you can
    store in a common log table.  (I've not found a way to do this without
    inserting one row for each column being logged, though.)
--
Mike Nolan

Thanks for the response.  I was pretty sure it couldn't be done the way I wanted to but felt I would ask anyway.

Thanks again,
Duane

-----Original Message-----
From: Mike Nolan [mailto:nolan@gw.tssi.com]
Sent: Tuesday, June 01, 2004 3:04 PM
To: DLee@mail.maricopa.gov
Cc: pgsql-general@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Trigger & Function

> 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". 

You're trying to insert record data into a text field, that doesn't work.
OLD and NEW can be used as either record identifiers (as in RETURN OLD)
or column qualifiers (as in OLD.colname), but you can't intermingle them.

I don't think postgres (pl/pgsql) has row-to-variable and variable-to-row
functions like serialize and unserialize, that's probably what you'd need.
It would probably be necessary to write something like that in C, since
at this point pl/perl cannot be used for trigger functions. 

I've not tried using pl/php yet, the announcement for it says it can be
used for trigger functions. 

My first thought is that even if there was a serialize/unserialize
capabiity you might be able to write something using it that creates
the log entry but not anything that allows you to query the log for
specific column or row entries.

It would probably require a MAJOR extension of SQL to add it to pg,
as there would need to be qualifiers that can be mapped to specific
tables and columns.  Even if we had that, storing values coming from
multiple tables into a single audit table would present huge challenges.

I've found only two ways to implement audit logs:

1.  Have separate log tables that match the structure of
    the tables they are logging.

2.  Write a trigger function that converts columns to something you can
    store in a common log table.  (I've not found a way to do this without
    inserting one row for each column being logged, though.)
--
Mike Nolan