From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Phuong Ma <pma(at)commandprompt(dot)com> |
Cc: | pgsql sql <pgsql-sql(at)postgresql(dot)org>, Postgres-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: trigger on DELETE |
Date: | 2001-07-11 23:20:11 |
Message-ID: | Pine.BSF.4.21.0107111617070.1320-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
On Wed, 11 Jul 2001, Phuong Ma wrote:
> 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.
I believe you want to use OLD rather than NEW for a delete trigger.
From | Date | Subject | |
---|---|---|---|
Next Message | Kristis Makris | 2001-07-11 23:23:48 | Re: trigger on DELETE |
Previous Message | Phuong Ma | 2001-07-11 22:56:55 | trigger on DELETE |
From | Date | Subject | |
---|---|---|---|
Next Message | Kristis Makris | 2001-07-11 23:23:48 | Re: trigger on DELETE |
Previous Message | Phuong Ma | 2001-07-11 22:56:55 | trigger on DELETE |