Oracle updating trigger
Let us take a simple scenario in which we have to know total number of invalid objects after any object status is updated to ‘INVALID’. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Why this is a problem when we are using ‘FOR EACH ROW’ clause?
As per Oracle documentation, the session, which issues a triggering statement on the table, cannot query the same table so that trigger cannot see inconsistent data.
In this blog post, we will cover why this error occurs and how we can resolve it using different methodology.
Let us see in this case how a compound trigger can resolve mutating table error.
There are other ways also to resolve mutating table error using temporary tables but we have discussed common ones in this blog post.
create or replace trigger update_emp_thru_outemp_view instead of update on outemp referencing new as new begin update emp set ename = :new.ename, empno = :new.empno, job = :new.job, mgr = :new.mgr, hiredate = :new.hiredate, sal = :new.sal, comm = :new.comm, deptno = ( select deptno from dept where dname = :new.dname ) where empno = :old.empno; if ( sql%rowcount = 0 ) then raise_application_error ( -20001, 'Error updating the outemp view !!!
So one has to be very careful when using this approach.
In version 11g, Oracle made it much easier with introduction of compound triggers.Constraints are declaractions of conditions about the database that must remain true.