Oracle trigger inserting or updating

Posted by / 03-May-2020 06:26

In such a situation, when there is a row inserted in table 'abc', trigger A fires and will update table 'xyz'.When the table 'xyz' is updated, trigger B fires and will insert a row in table 'abc'.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

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 !!!

' ); end if; end; select * from emp where ename = 'C Farmer'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- 3456 C Farmer DBA 7839 01-JUL-02 4000 4 40 1 row selected.

4) Finally the AFTER statement level trigger fires.

For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.

oracle trigger inserting or updating-90oracle trigger inserting or updating-42oracle trigger inserting or updating-89

1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

One thought on “oracle trigger inserting or updating”