Postgres Pro
Downloads
Home   >   mailing lists

Thread: trigger on DELETE

trigger on DELETE

From
Phuong Ma
Date:
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.

Re: trigger on DELETE

From
Stephan Szabo
Date:
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.

Re: trigger on DELETE

From
"Thalis A. Kalfigopoulos"
Date:
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