Re: A generic trigger?

On Sunday 14 September 2003 02:13, ow wrote:
> Hi,
>
> Am looking for a way to minimize the amount of fuctions that support
> triggers. E.g., there's "company" and "company_backup" tables. Update
> trigger on the "company" table will put a record in the "company_backup"
> table whenever "company" record is updated.
>
> The problem is that there's quite a few other tables for which similar
> backup logic has to be done (e.g. "custormer" and "customer_backup", etc).
> The backup logic is the same, only structure of the tables changes.
>
> Is there a way to write a generic trigger/function that would deal with
> backup regardless of the table structure?
>
> Thanks in advance.
>
>
>

Yes it is possible and I've done it. The reason I'm not using it is because I
wrote it in Pl/Python and if you attach the same trigger to more than one
table in the same transaction pg/python (actually the entire server crashes
but thats not the point) crashes. Well it did when I last tested it in early
versions. I'm still thinking of getting around to rewriting it in a language
without this bug, since nobody sounds like they are going to fix it. C might
be best!
This version inserts all the history in the same table. But since its broke
anyway changing it to insert into different tables should not be too
difficult.
There are some scripting languages where somthing don't work hense why I
chose pl/python.... The trigger/function is below although it should be in
the archives somwhere as well. Full problem with it can be seen of Bugs....

Peter Childs

-- CREATE TABLE history ( tab text field text
action text before text
after text occured timestamp without time zone key
text who text );

DROP INDEX history_tab;
DROP INDEX history_tab_field;
DROP INDEX history_tab_key;
DROP INDEX history_tab_who;
DROP INDEX history_who;
CREATE INDEX history_tab on history(tab);
CREATE INDEX history_tab_field on history(tab,field);
CREATE INDEX history_tab_key on history(tab,key);
CREATE INDEX history_tab_who on history(tab,who);
CREATE INDEX history_who on history(who);

CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
lookup = "new"
elif TD["event"] == "DELETE":
lookup = "old"
else:
lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc) THEN
a.attname ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid), POSITION(''('' in
pg_catalog.pg_get_indexdef(attrelid))) END as pkey, a.atttypid::int,
c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE c.oid = " +
TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid = c2.oid and
a.attrelid = i.indexrelid and NOT a.attisdropped and i.indisprimary ORDER BY
i.indisprimary DESC, i.indisunique DESC, c2.relname;")
if len(p) > 0:
pkey = TD[lookup][p[0]["pkey"]]
ppkey = p[0]["pkey"]
else:
pkey = ""
ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"] +
";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history
(tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
old = ""
new = pkey
plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
for key in TD[lookup].keys():
dont = 0
if TD["event"] == "INSERT":
old = ""
new = TD["new"][key]
if new == None:
dont = 1
elif TD["event"] == "UPDATE":
old = TD["old"][key]
new = TD["new"][key]
else:
old = TD["old"][key]
new = ""
if old == None:
old = "Null"
if new == None:
new = "Null"
if new == old:
dont = 1
if not(dont):
plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';

CREATE TRIGGER history_update AFTER INSERT OR UPDATE OR DELETE ON
account_history
FOR EACH ROW EXECUTE PROCEDURE history_update();