From: | Phuong Ma <pma(at)commandprompt(dot)com> |
---|---|
To: | pgsql sql <pgsql-sql(at)postgresql(dot)org>, Postgres-general <pgsql-general(at)postgresql(dot)org> |
Subject: | trigger on DELETE |
Date: | 2001-07-11 22:56:55 |
Message-ID: | 3B4CD9B7.B79DF11D@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-07-11 23:20:11 | Re: trigger on DELETE |
Previous Message | Peter Eisentraut | 2001-07-11 22:48:08 | Re: Re: Debian's PostgreSQL packages |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-07-11 23:20:11 | Re: trigger on DELETE |
Previous Message | Kristis Makris | 2001-07-11 18:13:41 | Re: [BUGS] ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found |