Re: Work Around for Mutating Trigger Error

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

Basic idea
Buffer the primary keys of tablea affected in a pl/sql table in your for each row statement
in an after delete statement level trigger traverse the table and do whatever you need to do.
http://asktom.oracle.com should have examples.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Jul 18 2001 - 10:26:29 CEST