Re: Triggers and mutating tables

A copy of this was sent to "Pete Chiccarino" <chiccarinop_at_firstenergycorp.com> (if that email address didn't require changing) On Thu, 11 Nov 1999 12:38:47 -0500, you wrote:

>I have 3 tables - A, B and C. A and B are in a
>parent/child relationship with A being the parent.
>C contains summary rows of data and is updated
>by triggers whenever A and B are updated.
>
>I would like to set up triggers to do the following:
>
>1. When a row is deleted from A, delete corresponding
> entries in B and C.
>2. When a row is deleted from B, delete corresponding
> entries in C.
>

add "On delete cascade" to the foreign key constraints from C to B and B to A and it'll just happen for you. Deletes from A will cascade to B and then to C. Deletes on B will cascade to C.

>The problem is that when I delete from A, the triggers
>fail because C is considered to be mutating, since
>the delete on A triggered a delete on B which
>triggered a delete on C.
>
>Is there any way to tell Oracle to ignore mutating table
>problems, and put the appropriate error checks in my
>triggers ?
>
>Thanks,
>
>Pete
>pchiccarino_at_usaNOSPAM.net
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 11 1999 - 12:01:30 CST