Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER

Any DML statement applies to all qualifying rows as to one entity. At the same time FOR EACH ROW triggers allow to execute trigger body on per row basis. This creates a "phylosophical" question. What is the state of trigger table? We did not apply DML statement to all qualifying rows yet, therefore results should not be seen even by our session. At the same time we already applied changes to some rows and trigger table state is not the same it was before the statement. Oracle calls such table state as "mutating" table. You can not even query it.The contents of "mutating" table is undefined. Your row level trigger tries to update table T which is trigger table and therefore is "mutating". Now assume "mutating" table is not an issue. Would your trigger work as expected? No. EXCEPTION clause in a trigger body allows to process exceptions in PL/SQL block associated with the trigger. By raising exception in trigger body you are not cancelling DML statement (DELETE in your case) for a corresponding row. Therefore row would be still deleted. Bottom line.Triggers do not work the way . Triggers can not cancel triggering event.

Solomon Yakobson.

In article <34657FD6.472C9E0F_at_hadassah.org.il>,   Isaac Chocron <itshak_at_hadassah.org.il> wrote:
>
> This is a multi-part message in MIME format.
> --------------6F6C7A897F0486F7AF1AE39D
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> I intented to replace a phisical delete by a logical delete on my table
> T.
> I definided a Before delete trigger that performs:
>
> DECLARE
> NO_DELETE exception;
> Begin
> raise NO_DELETE;
> EXCEPTION
> when NO_DELETE then
> UPDATE T set HIDDEN_DATE = SYSDATE where rowid = :old.rowid;
> End;
>
> But I receive an ORA-04091: table T is mutating, trigger/function may
> not see it.
>
> A possible solution is to batch a database job, but I prefer a local
> solution to this error.
>
> Do you know another possible solution ?
>
> --------------6F6C7A897F0486F7AF1AE39D
> Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Isaac Chocron
> Content-Disposition: attachment; filename="vcard.vcf"
>
> begin: vcard
> fn: Isaac Chocron
> n: Chocron;Isaac
> org: Hadassah Hospital
> adr: p.o.b 12000;;;Jerusalem;;91120;Israel
> email;internet: itshak_at_hadassah.org.il
> title: System Designer
> tel;work: 02-6778113
> x-mozilla-cpt: ;0
> x-mozilla-html: FALSE
> version: 2.1
> end: vcard
>
> --------------6F6C7A897F0486F7AF1AE39D--

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet