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