Обсуждение: trigger on DELETE
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.
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.
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