Re: Work Around for Mutating Trigger Error

Have you ever used PRAGMA AUTONOMOUS_TRANSACTION?

I think it became available in 8i and allows the trigger(or other PL/SQL construct) to run in a separate transaction.

Otherwise, the only other way I know of to get around the dreaded TABLE IS MUTATING error is to write 3 different triggers:

1. Write a BEFORE DELETE statement trigger to initialize an array to null
2. Write an AFTER DELETE FOR EACH ROW trigger to populate the array with the PK of the records being deleted
3. Write an AFTER DELETE statement trigger to parse the array and perform the additional steps required

Hope this helps.

Anthony DiAngelus

wkooiman_at_earthlink.net (Will) wrote:
>gregstarnes_at_hotmail.com (greg) wrote in message
 news:<c61056f4.0107171349.2ddf66fa_at_posting.google.com>...
>> Here's What Im trying to accomplish:
>>
>> CREATE TRIGGER
>> AFTER DELETE ON table_A
>> FOR EACH ROW
>>
>> BEGIN
>> SELECT FROM table_A
>> ...
>> IF SQL%ROWCOUNT = 0
>> THEN ...
>> ELSE DELETE FROM table_B
>> END IF;
>> END;
>>
>> The concept here being that if I delete a row from table_a, I need to
>> look at the rest of table_a table for additional requirements. If
>> those requirements are met, I need to delete from another table.
>> I understand that this is not possible: reading from a table through a
>> trigger that has the table locked for the delete. Anyone know any
>> easy workarounds. There is a LOT of Reporting code around this table,
>> and I would hate to have to have the reporting team rewrite all their
>> code so that I can change their table.
>>
>> Thanks in advance!
>> Greg
>
>Why do you have to do it in triggers? Do it the old fashioned way.
>You know, the way you'd have done it if there were no triggers.
>
> delete from table_a;
> delete from table_b where ...;
>
>I hope this doesn't come across as being snotty. I'm pretty tired
>right now, so the dry humor flows unabated.
>
>Will.
Received on Wed Aug 08 2001 - 08:25:46 CEST