trigger on DELETE

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.

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.

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