Re: Trigger Question

Philip Reed <preed_at_mail.xa.com> wrote in article <322F1572.4081_at_mail.xa.com>...
> 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.
>

One major problem with your plan. You cannot select from the same table that initiates the row trigger. It is the "mutating table problem".

A simpler approach would be to have the before delete trigger perform the following:

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

It's simpler and you don't need to a second SQL statement. Received on Mon Sep 09 1996 - 00:00:00 CEST