I am revisiting the age-old audit table issue, and am curious to see
whether I can get away with not writing custom trigger functions for
every table being audited.
My design has a foo_audit schema for each schema foo containing tables
to be audited, so triggers on foo.bar will insert rows into
foo_audit.bar, which is a clone of foo.bar with some extra columns added.
I would like to have three generic functions for insert/update/delete,
instead of three custom functions for each audited table. The problem,
of course, is handling the different column structures. I suppose I
could go look things up in the catalog and generate dynamic sql based on
the tables structure recorded there, but that seems like way too much
overhead for an audit system.
I tried something like this, but it didn't like the reference to "new"
inside the execute string:
CREATE OR REPLACE FUNCTION meta.audit_insert_trig() RETURNS trigger SECURITY DEFINER
AS $PROC$
DECLARE varschema TEXT;
BEGIN varschema = tg_argv[0]; if varschema is null or char_length(varschema) = 0 then raise exception 'must create
triggerwith schema name as arg'; end if; new.auditrowid := nextval('meta.auditrowid_seq'); execute 'insert into '
||quote_ident(varschema||'_audit.'||tg_relname) || ' select now(),NULL,''I'',new.*'; return new;
END
$PROC$ LANGUAGE plpgsql;
The error I get is: "ERROR: NEW used in query that is not in a rule",
which doesn't seem like an unreasonable limitation. Is there any way to
accomplish this (efficiently) in a generic function, or should I go back
to my previous implementation with dozens of custom trigger functions?
While I've got your attention, I'll toss in some related questions:
- What is the efficiency tradeoff of having a single combined function
with a conditional to detect insert/update/delete, versus having three
specific functions that duplicate some common code but don't have the
conditional?
- Is there an easier way to get the name of the schema associated with a
table from inside a trigger, rather than the trigger argument kludge I
used above?
Thanks,
-jbp
--
Jay Parker - UALR Computing Services - Networks Project Manager
jbparker@ualr.edu - http://www.ualr.edu/jbparker - 501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost