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.
