Received: from mail-pop01.one.net.au (mail-pop01.one.net.au [203.101.17.170]) by hub.org (8.10.1/8.10.1) with SMTP id e7LMsaT96107 for ; Mon, 21 Aug 2000 18:54:37 -0400 (EDT) Received: (qmail 2354 invoked from network); 21 Aug 2000 14:26:30 -0000 Received: from async165-syd-isp-236.nas.one.net.au (HELO darthvader.e-magine.com.au) (61.12.14.166) by mail-pop01.one.net.au with SMTP; 21 Aug 2000 14:26:30 -0000 Message-Id: <4.3.2.7.0.20000822003607.00b0d6b0@mail.e-magine.com.au> X-Sender: e-magine@mail.e-magine.com.au X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Tue, 22 Aug 2000 00:48:27 +1000 To: pgSQL From: Keith Wong Subject: Copying data with triggers In-Reply-To: References: <002f01c00af3$4efce0a0$0b01a8c0@ivsol> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200008/212 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_data text, aud_operation_type, aud_log_time timestamp default now() ) 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 Info for 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.