Re: Trigger function to audit any kind of table

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461