Hi all,
In the database I'm designing, I want to have audit tables that keep a log
of all inserts, updates and deletes that occur
on any table.
e.g. If i had a table Info,
create table Info ( info_id SERIAL, some_data text
)
I would also have a corresponding audit table
create table AudInfo (aud_key_id SERIAL, info_id int4,
some_datatext, aud_operation_type, aud_log_time timestamp
defaultnow()
)
now I tried creating a trigger on Info, so that whenever an insert occurs,
the records are copied to the audit table.
create function tp_info () returns opaque as 'begin -- insert into audit table insert into AudInfo (info_id,
some_data,aud_operation_type) values
(new.info_id, new.some_data, ''i''); return new;end;
' language 'plpgsql';
create trigger tp_info before insert on Infofor each row execute procedure tp_info();
This doesn't work however. A parse error occurs in the first line.
I suspect that I cannot insert a record into another table in a trigger.
I'm not sure why though.
Anybody else done similar operations within a trigger procedure? Or know of
a work around?
Cheers,
Keith.
