Oracle trigger if inserting or updating
With the compound trigger, both the statement-level and row-level action can be put up in a single trigger.Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.As we’ve already created both the tables in above example, we will directly write a trigger for ‘AFTER UPDATE’.CREATE or REPLACE TRIGGER emp_after_update AFTER UPDATE OF empid ON emp FOR EACH ROW DECLARE BEGIN update emp_backup set empid = :new.empid where empid = :old.empid; DBMS_OUTPUT.By commenting, you are accepting the IBM commenting guidelines and the DISQUS terms of service.The CREATE TRIGGER statement has a lot of permutations, but the vast majority of the questions I'm asked relate to basic DML triggers.Lets create a compound trigger for auditing a large table called ’employees’. SALARY); v_emp_changes(v_index).to_value := to_char(: NEW. SALARY); v_emp_changes(v_index).action := 'Update'; END IF; END IF; IF v_index = v_threshhold THEN flush_logs(); END IF; END AFTER EACH ROW; -- AFTER STATEMENT Section: AFTER STATEMENT IS BEGIN flush_logs(); END AFTER STATEMENT; END aud_emp; / INSERT INTO employees VALUES (1, 'emp1', 10000); INSERT INTO employees VALUES (2, 'emp2', 20000); INSERT INTO employees VALUES (3, 'emp3', 16000); UPDATE employees SET salary = 2000 WHERE salary EMP_ID, UPD_BY, UPD_DT, ACTION, FIELD, FROM_VALUE, TO_VALUE 1, Aditya,1/22/2014 AM, Create,*, NULL,* 2, Aditya,1/22/2014 AM, Create,*, NULL,* 3, Aditya,1/22/2014 AM, Create,*, NULL,* 2, Aditya,1/22/2014 AM, Update, SALARY,20000,2000 3, Aditya,1/22/2014 AM, Update, SALARY,16000,2000 Now any changes in any field of employees will to be written in aud_emp table.
FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.If no WHEN clause is supplied, the SQL statements are executed every time the trigger fires.The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.