Re: Is there a way to conitionaly fire triggers ??

From: <smb_slb_at_my-dejanews.com>
Date: Tue, 18 May 1999 23:54:31 GMT
Message-ID: <7hsujm$gc$1@nnrp1.deja.com>

Consider this:

When you want to delete the row permanently, just delete the row in the mirror table instead of the primary table. In order for this to work, you need to do two things:

• put a delete trigger in the mirror that will delete the corresponding row in the primary.
• protect the copy statement in the original table's delete trigger to catch the mutatation error you will get when the primary tables's delete trigger fires.

This way, when you delete from the primary it will copy the row into the mirror. When you delete from the mirror, it will delete the row from the primary, which will cause the primary's delete trigger to try to insert a row into the mirror, which will fail due to a table mutatation error, but you throw away the error (but raise all other errors so you still catch any real problems).

Make sure you put only the "copy to mirror" logic in the catch block, so other statements can execute normally. Also, if there is any cascade-delete logic you want to put in primary, and this cascades to other tables with the same "backup" behavior, you'll want to put the same cascade logic in the mirror tables so deletes cascade correctly.

---

Like this:

---

CREATE TRIGGER tD_primary after delete on primary for each row BEGIN
   BEGIN

      INSERT INTO mirror
      VALUES (:old.col1, :old.col2, ...);
   EXCEPTION
      WHEN OTHERS THEN
         IF SQLCODE = -4091 THEN
            -- This is a table mutation error.  Ignore it since
            -- it will happen when we delete this row from the
            -- mirror's delete trigger
            NULL;
         ELSE
            RAISE;
         END IF;

   END;    <Other trigger statements go here>
END; CREATE TRIGGER tD_mirror after delete on mirror for each row BEGIN
  DELETE primary where col1 = :old.col1 and (...); END; In article <926630827.21277.0.nnrp-08.d4e48d0d_at_news.demon.co.uk>,   "Matt Randle" <matt_at_imat.demon.co.uk> wrote:
> Hello,
>
> I have a slight problem with Oracle triggers.
>
> A requirement of the system we are building is that you can delete
data
> either semi-permanently (it gets copied to an exact mirror of the
actual
> tables) or permanently (it is removed without trace).
>
> The easiest way to do the semi-permanent delete is to use On Delete
triggers
> to copy deleted data to the deleted tables. However, if I do this
how do I
> allow for permanent delete - ie. is there anyway of stopping the
trigger
> from firing and copying the deleted data ??
>
> Matt.
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 18 1999 - 18:54:31 CDT