Re: Trigger Question

In <322F1572.4081_at_mail.xa.com>, Philip Reed <preed_at_mail.xa.com> writes:
>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.

Try:

     Insert into stuff_deleted(x, y, z, deleted_date, deleted_by)
       values (:old.x, :old.y, :old.z, sysdate, user);

M.Landa Received on Mon Sep 09 1996 - 00:00:00 CEST