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 likeCREATE 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