pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
trigger 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