Triggers, functions and column names: a poser
here's a nice trigger problem for the weekend ;-)
I have a database (7.1.3) with a number of tables, to some of which
I wish to apply some form of logging / auditing.
(The reasons for this are manifold and related to the
business logic of the organisation which owns the database).
Specifically, for certain tables on INSERT, UPDATE or DELETE I
want to write the following to a seperate logging table:
the name of the table (relation); the action performed;
the primary key of the row affected; and a timestamp.
Rather than create a seperate RULE for each action on
each table (pain to maintain) I would like to create a
PL/PgSQL function to be called by triggers for the relevant tables.
This is the function:
CREATE FUNCTION update_log()
RETURNS opaque
AS '
DECLARE
qid INTEGER;
BEGIN
IF TG_OP = ''DELETE''
THEN qid := OLD.p_id;
ELSE qid := NEW.p_id;
END IF;
INSERT INTO change_log
(row_operation, table_name, id, created)
VALUES(TG_OP, TG_RELNAME, qid, now());
RETURN new;
END;'
LANGUAGE 'plpgsql';
This is the table 'change_log':
CREATE TABLE change_log (
id integer,
table_name varchar(32),
row_operation varchar(7),
created timestamp,
CHECK (row_operation in ('INSERT','UPDATE','DELETE'))
)
For a (simplified) table like this:
CREATE TABLE product (
p_id SERIAL PRIMARY KEY,
p_name varchar(64)
)
I would create the following trigger:
CREATE TRIGGER trigger_product_log
AFTER INSERT OR UPDATE OR DELETE
ON product
FOR EACH ROW
EXECUTE PROCEDURE update_log();
So far so good. Unfortunately each table's primary
key is labelled differently, i.e. _not_ 'id'. I would
therefore like to find a way to determine the value
written into change_log.id without having to hard-wire
the row name into the function.
(I could of course rebuild the database with all
relevant primary keys renamed as 'id', and
rewrite the overlying application, but I think I'd
rather open up that large writhing can of worms in
the corner now past its open-by-date ;-)
I'd guess there are three approaches to doing this:
1) provide the id from the trigger, e.g. something
like
CREATE TRIGGER trigger_product_log
(...)
EXECUTE PROCEDURE update_log(p_id);
so that update_log finds the id in TG_ARGV[0];
2) provide the column name from the trigger, e.g.
CREATE TRIGGER trigger_product_log
(...)
EXECUTE PROCEDURE update_log('p_id')
so that update_log can do something like this:
(...)
DECLARE
qid INTEGER;
BEGIN
qid := NEW.TG_ARGV[0]
(...)
3) use some 'magic' function which fetches the primary
key of the row referred to by 'OLD' or 'NEW'...
Alas I haven't found any documentation or anything in
the various archives on how to do this, and random
"guess-a-syntax" attempts have also proved remarkably unsuccessful.
Is what I am trying to do possible, and if so how; or am
I barking up the wrong line of enquiry entirely?
Any advice gratefully accepted
yrs
Grant Table
grant.table@easypublish.de
"Table Design by Name and By Nature"
On Sat, 17 Nov 2001, Grant Table wrote:
here's a nice trigger problem for the weekend ;-)
Specifically, for certain tables on INSERT, UPDATE or DELETE I
want to write the following to a seperate logging table:
the name of the table (relation); the action performed;
the primary key of the row affected; and a timestamp.Rather than create a seperate RULE for each action on
each table (pain to maintain) I would like to create a
PL/PgSQL function to be called by triggers for the relevant tables.
Hi,
I had just written one such beast recently which in addition to
what you want also writes the values of the updated/inserted fields as a
serialised string. I wrote this primarily to replicate two databases with
a perlscript that reads this "logtable" and then talks to a remote
database with an expect-send sequence. I am currently writing an article
describing this which I was planning to submit to the
techdocs.postgresql.org site. IAC here's the code. I have also included
the droptrigger utility which well .. drops the triggers should something
bad happen. Hope you find this useful.
-- Setuptriggers
drop function setuptriggers();
create function setuptriggers() returns int as '
declare
fbui text;
fbdel text;
tbui text;
tbdel text;
tresult record;
cresult record;
pkeyname name;
typename name;
dropname name;
dummy record;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
select into pkeyname c.attname from pg_class a, pg_index b, pg_attribute c
where a.relname = tresult.relname and
a.oid = b.indrelid and
a.oid = c.attrelid and
b.indkey[0] = c.attnum and
b.indisprimary=''t'';
if pkeyname is not null and tresult.relname != ''logtable'' and tresult.relname !~ ''^web_'' then
fbui := ''
create function logui_'' || quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
serialized text;
currtime timestamp;
separator text;
op integer;
begin
currtime := ''''''''now'''''''';
separator := chr(178);
if TG_OP = ''''''''INSERT'''''''' then
op := 1;
else if TG_OP = ''''''''UPDATE'''''''' then
op := 2;
end if;
end if;
serialized := '''''''''''''''';
'';
for cresult in select * from pg_class a, pg_attribute b
where a.relname = tresult.relname and
a.oid = b.attrelid and
b.attnum > 0
order by b.attnum
loop
select into typename aa.typname from pg_type aa, pg_attribute bb, pg_class cc
where bb.attname = cresult.attname and
bb.atttypid = aa.oid and
bb.attrelid = cc.oid and
cc.relname = tresult.relname;
if typename !~ ''^bool'' then
fbui := fbui || '' if NEW.'' || quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator || '''''''''' || quote_ident(cresult.attname) || ''='''''''' || NEW.'' || quote_ident(cresult.attname) || '';
end if;
'';
else
fbui := fbui || '' if NEW.'' || quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator || '''''''''' || quote_ident(cresult.attname) || ''='''''''' || case when NEW.'' || quote_ident(cresult.attname) || '' then ''''''''TRUE'''''''' else ''''''''FALSE'''''''' end;
end if;
'';
end if;
end loop;
fbui := fbui || '' insert into logtable (keyid, tablename, pkeyname, value, updatetime, status) values (NEW.''|| quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''', '''''''''' || quote_ident(pkeyname) || '''''''''', serialized, currtime, op);
return new;
end;''''
language ''''plpgsql'''';'';
dropname := ''logui_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
fbui := ''drop function '' || quote_ident(dropname) || ''();
'' || fbui;
end if;
execute fbui;
fbdel := ''
create function logdel_'' || quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
currtime timestamp;
begin
currtime := ''''''''now'''''''';
insert into logtable (keyid, tablename, pkeyname, value, updatetime, status) values (OLD.''|| quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''', '''''''''' || quote_ident(pkeyname) || '''''''''', NULL, currtime, 3);
return old;
end;''''
language ''''plpgsql'''';'';
dropname := ''logdel_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
fbdel := ''drop function '' || quote_ident(dropname) || ''();
'' || fbdel;
end if;
execute fbdel;
tbui := ''create trigger fui_'' || quote_ident(tresult.relname) || '' before insert or update on '' || quote_ident(tresult.relname) || ''
for each row execute procedure logui_'' || quote_ident(tresult.relname) || ''();'';
dropname := ''fui_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
tbui := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname) || '';
'' || tbui;
end if;
execute tbui;
tbdel := ''create trigger fd_'' || quote_ident(tresult.relname) || '' before delete on '' || quote_ident(tresult.relname) || ''
for each row execute procedure logdel_'' || quote_ident(tresult.relname) || ''();'';
dropname := ''fd_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
tbdel := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname) || '';
'' || tbdel;
end if;
execute tbdel;
end if;
end loop;
return 1;
end;'
language 'plpgsql';
-- Drop triggers
drop function droptriggers();
create function droptriggers() returns int as '
declare
tresult record;
dropname name;
dropcommand text;
dummy record;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
dropname := ''logui_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
dropcommand := ''drop function '' || quote_ident(dropname) || ''()'';
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''logdel_'' || tresult.relname;
select into dummy * from pg_proc where proname = dropname and pronargs = 0;
if found then
dropcommand := ''drop function '' || quote_ident(dropname) || ''()'';
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''fui_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
dropcommand := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname);
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
dropname := ''fd_'' || tresult.relname;
select into dummy * from pg_trigger where tgname = dropname;
if found then
dropcommand := ''drop trigger '' || quote_ident(dropname) || '' on '' || quote_ident(tresult.relname);
raise notice ''Executing %'',dropcommand;
execute dropcommand;
end if;
end loop;
return 1;
end;'
language 'plpgsql';
On Saturday 17 November 2001 04:53, Gurunandan R. Bhat wrote:
Hi,
I had just written one such beast recently which in addition to
what you want also writes the values of the updated/inserted fields as a
serialised string. I wrote this primarily to replicate two databases with
a perlscript that reads this "logtable" and then talks to a remote
database with an expect-send sequence. I am currently writing an article
describing this which I was planning to submit to the
techdocs.postgresql.org site. IAC here's the code. I have also included
the droptrigger utility which well .. drops the triggers should something
bad happen. Hope you find this useful.
(code snipped)
Hi
many thanks for the reply and the code. Pointed me in the right direction :-))
(as it happens a selective limited replication probably using a
cronjob-triggered Perl script is one aim of the action).
Thanks again
Grant.