pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger on DELETE
4+ messages / 4 participants
[nested] [flat]
* trigger on DELETE
@ 2001-07-11 22:56 Phuong Ma <pma@commandprompt.com>
2001-07-11 23:20 ` Re: trigger on DELETE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-11 23:23 ` Re: trigger on DELETE Kristis Makris <kristis.makris@datasoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Phuong Ma @ 2001-07-11 22:56 UTC (permalink / raw)
To: pgsql-sql; Postgres-general <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger on DELETE
2001-07-11 22:56 trigger on DELETE Phuong Ma <pma@commandprompt.com>
@ 2001-07-11 23:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-12 16:03 ` Re: trigger on DELETE Thalis A. Kalfigopoulos <thalis@cs.pitt.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2001-07-11 23:20 UTC (permalink / raw)
To: Phuong Ma <pma@commandprompt.com>; +Cc: pgsql-sql; Postgres-general <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger on DELETE
2001-07-11 22:56 trigger on DELETE Phuong Ma <pma@commandprompt.com>
2001-07-11 23:20 ` Re: trigger on DELETE Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-07-12 16:03 ` Thalis A. Kalfigopoulos <thalis@cs.pitt.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Thalis A. Kalfigopoulos @ 2001-07-12 16:03 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Phuong Ma <pma@commandprompt.com>; pgsql-sql; Postgres-general <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger on DELETE
2001-07-11 22:56 trigger on DELETE Phuong Ma <pma@commandprompt.com>
@ 2001-07-11 23:23 ` Kristis Makris <kristis.makris@datasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Kristis Makris @ 2001-07-11 23:23 UTC (permalink / raw)
To: Phuong Ma <pma@commandprompt.com>; +Cc: pgsql-sql; Postgres-general <pgsql-general@postgresql.org>
> 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.
Try using the OLD variable instead.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-07-12 16:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-11 22:56 trigger on DELETE Phuong Ma <pma@commandprompt.com>
2001-07-11 23:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-12 16:03 ` Thalis A. Kalfigopoulos <thalis@cs.pitt.edu>
2001-07-11 23:23 ` Kristis Makris <kristis.makris@datasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox