Обсуждение: 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.