Re: Trigger Mutating

Oracle's definition:  

"A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress."

In your case, you are attempting to read and sum the salaries of employees belonging to a certain department in the emp table, in which the INSERT is happening. What is tripping you up is that the trigger needs to update the salary for a particular department, hence you have it as a row trigger.

The way to get around this is to modify the trigger so that it stores the department which needs to be updated. One way to do this is by creating a package with an array of deptnos. Create a before insert STATEMENT trigger which initializes the package's array to empty. The after each row trigger adds the deptnos to the array. Now create an after STATEMENT trigger. The table is no longer mutating. The after STATEMENT trigger removes deptnos from the array one by one and updates my_dept. It may seem like a klug and it is. But it works and gets around the mutating table error.

  Kamal Shah
(shirushah_at_hotmail.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 %22SYSTEM.TRIG_NEW_EMP_IN%22, line 2
: ORA-04088: error during execution of trigger 'SYSTEM.TRIG_NEW_EMP_IN'

: What am I doing wrong, as this is not working?

: Thanks so much for your time & help.
: Shiru

: -**** Posted from Supernews, Discussions Start Here(tm) ****-
: http://www.supernews.com/ - Host to the the World's Discussions & Usenet

--

   Tom Pall, contract Oracle DBA Received on Mon Nov 30 1998 - 15:59:44 CST