Re: Need workaround for Oracle Mutating table

On 24 jan, 19:43, DA Morgan <damor..._at_psoug.org> wrote:
> hasta..._at_hotmail.com wrote:
> > On 23 jan, 18:30, DA Morgan <damor..._at_psoug.org> wrote:
> >> hasta..._at_hotmail.com wrote:
> >>> swapnil.k..._at_gmail.com a écrit :
> >>>> Thanks DA Morgan. It solved the problem. Thanks a lot for the help. I
> >>>> referred the "Autonomous Transaction" and thats what i needed. <I'll
> >>>> definately think over remodelling it.>
> >>> Swapnil, it is rare that an autonomous transaction is a
> >>> solution to the mutating table warning...
> >>> Could we see your solution ?
> >> You can find one example at:http://www.psoug.org/reference/table_trigger.html
>
> > Daniel, what is the exact specification of the code in the mutating
> > table example ? Is t2 supposed to record the history of the number of rows
> > of t2 ?
>
> In theory it should, AFTER INSERT, record the number of records it found
> in T1, thetableon which it is located, into T2 for auditing purposes.
>
> It is a rotten piece of code but it demonstrates the concept.

And it does not fulfill the specification, Daniel, as you know already.

truncate table t1;
truncate table t2;
insert into t1(x) values (1);
commit;
select count(*) from t1;

COUNT(*)

         1

select * from t2;

         X

         0

Solutions using autonomous transaction to go around the mutating table problem almost always fail to meet their spec, especially in the face of rollbacks and/or concurent updates and/or multiple rows DML statements.

I strongly suspect that I would have been able to break the OP code if he had posted it, and that the pattern http://asktom.oracle.com/tkyte/Mutate/index.html would have worked much better for him

Have a nice day (sunny over here :-))

• Raoul