Hi,
I'm trying to build an audit system for several tables. My idea was to use
triggers and plpgsql to record changes made to "important tables" to a
special audit table. My problem is that I don't want to create a separate
audit log table for each table that is being monitored. What I would like
to do is just cast the data from NEW.* or OLD.* to text and insert it into
a text column. Is this possible? I'm using version 8.1.5.
Example:
CREATE TABLE t1 (foo text, bar text);
CREATE TABLE t2 (id int, col timestamp);
CREATE TABLE audit (id int, optype char, time timestamp, user text, target
text, oldvalues text, newvalues text);
CREATE FUNCTION audit() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO audit(optype,time,user,target,newvalues) VALUES ('I ',
now(), current_user, TG_RELNAME, NEW.*::text);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
CREATE TRIGGER t1_audit AFTER INSERT OR UPDATE OR DELETE ON t1 FOR EACH
ROW EXECUTE PROCEDURE audit();
CREATE TRIGGER t2_audit AFTER INSERT OR UPDATE OR DELETE ON t2 FOR EACH
ROW EXECUTE PROCEDURE audit();
I guess the explanation is a bit vague, but I hope you got my point!
Regards
MP
