Thread: Trigger function to audit any kind of table

Hello,

I would like to know if it is possible to create a trigger function which does something like

CREATE OR REPLACE FUNCTION table_audit() RETURNS TRIGGER AS $table_audit$
BEGIN
  INSERT INTO audit SELECT TG_OP, current_timestampmp, current_user, OLD, NEW;
  RETURN NEW;
END $table_audit$ LANGUAGE plpgsql;

Can I create a table with those two RECORD pseudo-types or something compatible?

I'm using pgsql 8.1


Thanks.

On Tue, Jun 06, 2006 at 01:54:01PM -0500, Sergio Duran wrote:
> Hello,
>
> I would like to know if it is possible to create a trigger function which
> does something like
>
> CREATE OR REPLACE FUNCTION table_audit() RETURNS TRIGGER AS $table_audit$
> BEGIN
>  INSERT INTO audit SELECT TG_OP, current_timestampmp, current_user, OLD,
> NEW;
>  RETURN NEW;
> END $table_audit$ LANGUAGE plpgsql;
>
> Can I create a table with those two RECORD pseudo-types or something
> compatible?

What you're looking for is a pseudo-type, and you can't store them in a
column. Probably your best bet if you need to do this for a bunch of
tables is to write some code that will generate the trigger code for
you.

BTW, you can also do the auditing with rules. Just remember that you
can't reliably audit SELECTS, since someone could always do:

BEGIN;
SELECT ...
ROLLBACK;
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461