Error on trigger

[sovan chatt]

I have a trigger which fires before the insert and delete operation on a table,and before insertion the no of employees column(per department) will be increased by 1,before deletion the no. of employees column is going to be decreased by 1;

create or replace trigger t1 
before insert or delete on empdept for each row
begin
if inserting then
update empdept set noe=noe+1 where empno=:OLD.empno;
elsif deleting then
update empdept set noe=noe-1 where empno=:OLD.empno;
end if;
end

but the problem is if I try to delete or update I get the error
*
ERROR at line 1:
ORA-04091: table SCOTT.EMPDEPT is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T1", line 5
ORA-04088: error during execution of trigger 'SCOTT.T1'.What to do?

[Martin Vajsar]

You're modifying the same table in the trigger on which the trigger is defined. This can cause consistency issues and therefore the "mutating table" exceptions are generated to prevent such inconsistencies from happening. You might search for "mutating table" on http://asktom.oracle.com, it has been discussed there thoroughly and some workarounds have been offered there. I believe that Tom Kyte's position on this topic is that you should not use triggers at all, and if you do, you should not modify the table your trigger is defined on, exactly to avoid mutating table problem. It is very easy to make subtle and difficult-to-find bugs with triggers and you should know Oracle very well if you want to use them (at least so well to know about the mutating table problem before it jumps on you, that is).

Another consideration is that you're incrementing the noe column in the very row which is being deleted or inserted in your trigger, which can probably never work, and even if it did, it would not make any sense. Didn't you actually try to keep the count of related records in a parent table? Instead of triggers, I'd suggest to simply count the rows using the count function (it might be made faster by adding an index perhaps), or, if the table is really really huge, use materialized views (after learning about them).