Trigger Question

I was wondering if anyone could recommend the trigger I should use to perform the following function:

Table Stuff exists with columns x, y and z. Upon deleting a row from stuff I want to copy the contents of that row to a second table named Table_Stuff_Deleted so that we might have a record what has been removed from the table but has not yet been moved entirely into the obsoleted tables. Two new field then also needs added which hold the date of the deletion and of the user performing the deletion. The new fields will be named deleted_date and deleted_by.

In this example x is the key field.

What I've come up with (obvious note: all real column and table names have been replaced with the names from my example, but the structure of the problem and the solution should not be affected).

CREATE TRIGGER stuff_deleted_trigger
BEFORE DELETE
ON stuff
FOR EACH ROW
BEGIN
  INSERT INTO stuff_deleted(x, y, z)
  SELECT x, y, z FROM stuff
  WHERE x = :new.x;
  UPDATE stuff_deleted SET deleted_date = sysdate, deleted_by = user   WHERE x = :new.x;
END;
COMMIT; I will appreciate any assistance I can have in this matter.

Thank you.

Philip Reed,
Systems Analyst, Parallax Microsystems. Received on Thu Sep 05 1996 - 00:00:00 CEST