Thursday, February 27, 2014

What is Instead of Triggers?


Instead of Triggers:-
                    With the help of INSTEAD OF TRIGGER we perform DML on complex view

Examples:-

CREATE OR REPLACE VIEW XXC05_EMP_DETAIL1
AS
    (
      SELECT E.EMPLOYEE_ID,
             E.SALARY,
             MAX(D.DEPARTMENT_ID)
      FROM EMPLOYEES E,
      DEPARTMENTS D,
      LOCATIONS L
      WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
      AND D.LOCATION_ID = L.LOCATION_ID
      GROUP BY (E.EMPLOYEE_ID,SALARY)
      );
   
create trigger XXC05_INSTEAD_OF_TRI
instead of update on XXC05_EMP_DETAIL1
begin
    update employees
    set employee_id = :new.employee_id
    where employee_id =198;
end XXC05_INSTEAD_OF_TRI;


UPDATE XXC05_EMP_DETAIL1
SET SALARY = SALARY+(SALARY*COMMISSION_PCT)
WHERE EMPLOYEE_ID = 198;

Note:-  When we execute update command it update view.






No comments:

Post a Comment