From: | "Thalis A(dot) Kalfigopoulos" <thalis(at)cs(dot)pitt(dot)edu> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | Phuong Ma <pma(at)commandprompt(dot)com>, pgsql sql <pgsql-sql(at)postgresql(dot)org>, Postgres-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: trigger on DELETE |
Date: | 2001-07-12 16:03:53 |
Message-ID: | Pine.LNX.4.21.0107121158380.8482-100000@aluminum.cs.pitt.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
On Wed, 11 Jul 2001, Stephan Szabo wrote:
> 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.
Also you want to have your trigger fire AFTER DELETE and have it return NULL;
cheers,
thalis
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 2001-07-12 16:10:05 | Re: news server access down? |
Previous Message | Ben-Nes Michael | 2001-07-12 15:26:36 | Re: Pg7.2 (was: vacuum and 24/7 uptime) |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-07-12 16:57:01 | Re: Functions performed on intervals |
Previous Message | Patrik Kudo | 2001-07-12 15:16:19 | Re: C Functions |