Re: Mutating Trigger??

In my opinion, Oracle should have fixed this long ago. But they took the cheap, easy way out and just created an error message. The problem is that when you insert, update, or delete from the EMP table, the dbms doesn't know for sure if there is some other insert, update, or delete on EMP that is in an inconsistant state (not yet committed, and not yet rolled back), and whether or not that other one is going to be sucessful, so the sum(sal) from emp is ambigious at best, and inconsistant at worst, and Oracle doesn't want anyone to be able to sue them for an inconsistancy in the data values caused by their DBMS, so they created that error message.

However, if you can live with a small delay in getting the average salary on MY_DEPT updated, check out the use of DBMS PIPES. Have the trigger send a pipe message to a package that is constantly running, looking for the message. Once the package receives the message, it then computes the sum(sal) on all related EMP and updates MY_DEPT. This will work because DBMS Pipes are not subject to the same unit-of-work as the trigger. It also means that you have a very, very, slight chance that the sum(sal) on MY_DEPT can be off by one employee. However, in my opinion, the chance is so small, and the amount of error non-harmful, and the error is self-correcting (the next time another EMP gets inserted, updated, or deleted it will fix itself) that it is worth the very very small risk.

shirushah_at_my-dejanews.com wrote:

> This trigger should fire after a new employee is inserted into
> the EMP table. The trigger should update the tot_sal data in
> the MY_DEPT table for the inserted department only.
>
> Info in the table:
> emp - ename, sal, deptno
> Tom, 1000, 15
> John, 2000, 15
> Don, 1500, 10
>
> my_dept - dname, deptno, tot_sal
> Sales, 15, 3000
> R&D, 10, 1500
>
> This is what I have done:
>
> SQL>
> create or replace trigger trig_new_emp_in
> after insert on emp
> for each row
> begin
> update my_dept
> set tot_sal =
> (select sum(sal)
> from emp
> where deptno = :new.deptno)
> where deptno = :new.deptno;
> end trig_new_emp_in;
>
> -- This is my insert into the emp table which will fire the trigger
> SQL>
> insert into emp
> (empno, ename, sal, deptno)
> values (6969, 'Pam', 1000, 10)
>
> --This is the error
> ERROR at line 1:
> ORA-04091: table SYSTEM.EMP is mutating, trigger/function may not see it
> ORA-06512: at "SYSTEM.TRIG_NEW_EMP_IN", line 2
> ORA-04088: error during execution of trigger 'SYSTEM.TRIG_NEW_EMP_IN'
>
> If I create it as a statment level trigger, i.e I remove the line
> 'for each row' then I get this error:
>
> ERROR at line 1:
> ORA-04082: NEW or OLD references not allowed in table level triggers
>
> What am I doing wrong, as this is not working?
>
> Thanks so much for your time & help.
> Shiru
> ‰
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Nov 24 1998 - 22:24:27 CST