Received: from commandprompt.com (dsl081-185-190.sea1.dsl.speakeasy.net [64.81.185.190]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6BMtKa07714; Wed, 11 Jul 2001 18:55:20 -0400 (EDT) (envelope-from pma@commandprompt.com) Received: from commandprompt.com (filesrv.commandprompt.com [192.168.1.35]) by commandprompt.com (8.9.3/8.9.3) with ESMTP id PAA03671; Wed, 11 Jul 2001 15:53:28 -0700 Message-ID: <3B4CD9B7.B79DF11D@commandprompt.com> Date: Wed, 11 Jul 2001 15:56:55 -0700 From: Phuong Ma X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.3-20mdk i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql sql , Postgres-general Subject: trigger on DELETE Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/412 X-Sequence-Number: 12384 Hello everyone, I'm trying to define a trigger that copies the row to be deleted into another table (which is the inventory_audit table) before it does the delete from the original table (which is the inventory table). CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS ' BEGIN NEW.user_aud := current_user; NEW.mod_time := ''NOW''; INSERT INTO inventory_audit SELECT * FROM inventory WHERE id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER inv_audit_mod BEFORE DELETE ON inventory FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod(); Ok, the function works only with a trigger that is defined as ON INSERT OR UPDATE. If I try to define a trigger for ON DELETE and then delete a row from the table, there is nothing in the 'NEW' variable to return. I get an error message. If I define the function to return NULL, 0, or nothing, then it comes up with a type mis-match error. Is there anyone who can help? Thanks.