Instead of Trigger

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;

When we execute update command it update view data.

Advertisements