Oracle if updating then

Now, whenever possible, I will go very far out of my way to avoid a trigger.I have two primary reasons for avoiding triggers: Hopefully the first reason to avoid triggers, the maintenance headache, is fairly easy to see.Using the PROFILER_CONTROL procedure as an example look at Figure 1.PROCEDURE profiler_control( start_stop IN VARCHAR2, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS ret_code INTEGER; BEGIN ret_code:=dbms_profiler.internal_version_check; IF ret_code !If you need to determine if the value is changing, you'll still need to code comparison logic for :old and :new values. When row is inserted/updated/deleted in table1, the same action is also applied for table2.Another option that you have is CREATE MATERIALIZED VIEW for any base table.It is a different from normal view, in normal view oracle only saves the query of that view in dictionary so that whenever you run any select statement on the normal view oracle reads the query from data dictionary and extract the data from tables, views those are using in creation of view's query, But it is rverse in the Materialized view, oracle stores the data instead of storing query.

SQL Notice in the last update it indicated that x and y both were being updated (which they were), but the update didn't change the value (both x and y were 1 prior to the update which set them to 1). You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed.

For this purpse i am creating a trigger called 'Emp_Ins_Upd_Del_Trig', complete list as follows.

CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig BEFORE delete or insert or update on EMP FOR EACH ROW BEGIN if UPDATING then UPDATE emp_temp SET ename = :new.ename , job = :, mgr = :, hiredate = :new.hiredate , sal = :, comm = :, deptno = :new.deptno; end if; if INSERTING then INSERT INTO emp_temp VALUES( :new.empno , :new.ename , :, :, :new.hiredate , :, :, :new.deptno); end if; if DELETING then DELETE FROM emp_temp WHERE empno = ld.empno; end if; END; / You first must test it by inserting, deleting and updating rows on emp table, Once you satisfied then apply it in your program.

I like to write a trigger to insert, update, and delete a row.

Thanks shafique, Yes, a trigger associates with table.

Leave a Reply