Oracle updating trigger

01-Aug-2017 04:47

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.

oracle updating trigger-62

net validating checkbox

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.

oracle updating trigger-32

snl dating your dad

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.

Chapter 21, Context Integration the generic Spring application context support.… continue reading »

Read more

The price per minute is also a factor that can influence the number of those who decide to pay for a private show.… continue reading »

Read more

All you need to do is getting a nickname at the entrance of the site. Xat Chat is very popular on internet and there are many users on the site who are using this quality chat service.… continue reading »

Read more

Translating irregular heartbeats could lead to new insights for computer scientists and medical diagnostics, according to an international project led by a Professor of Digital Media at Queen Mary University of London (QMUL), which was presented today at the British Science Festival in Brighton (Friday 8 September).… continue reading »

Read more

You can use this option to share funnier picture while you chat with other chatters in our chat room in the main lobby chat window.… continue reading »

Read more

Experience sex chat at its best and give Sexchatster a try!… continue reading »

Read more

Dear members, Starting today, you'll notice that there's a padlock next to the domain name in the address bar.… continue reading »

Read more