Oracle - Trigger INSTEAD OF

[ Oracle - Trigger INSTEAD OF ]

 

 

INSTEAD OF Trigger ---------------------------------------------------------------------- 1. 개념 INSTEAD OF trigger는 Oracle8에서 새로이 소개된 방법으로, DML문장에 의해 직접 변경할 수 없는 view를 변경하기 위해 사용된다. 즉, base table이 fire 하는 trigger를 생성하는 것이 아니고 view를 대상으로 trigger를 생성하여 view에 대한 DML문장을 수행시 대신 trigger가 fire되어 base table을 직접 변경하게 되는 것이다. 기본적으로 DML이 불가능한 view는 다음 사항들을 포함하고 있는 경우이다. 이러한 사항을 포함한 view들에 대해서 instead of trigger를 생성하면 DML을 수행할 수 있게 된다. (1) DISTINCT operator (2) group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE등 (3) set operations: UNION, MINUS 등 (4) GROUP BY, CONNECT BY, START WITH clauses (5) ROWNUM pseudocolumn (6) join (updatable join view인 경우는 제한적으로 DML수행가능 <Bul:11642>참조) 2. EXAMPLE instead of trigger의 예를 다음과 같이 작성하였다. (1) base tables create table dept (deptno number(4) primary key, dname varchar2(14), loc varchar2(13)); create table emp (empno number(4), ename varchar2(10), sal number(7,2), mgr number(4), deptno number(2) ); (2) 직접 dml을 수행할 수 없는 view create view emp_dept as select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno=dept.deptno; [참고] 이 예에서 dept table의 deptno가 primary key나 unique로 선언되어 있다면 emp_dept view는 updatable join view가 되어 key-reserved table인 emp table 에 대한 dml은 trigger를 사용하지 않아도 직접 수행 가능하다. <Bul:11642>참조 (3) instead of trigger view에 DML을 수행시 내부적으로 수행되기를 원하는 logic을 임의로 user가 작성하면 된다. 아래의 예에서는 emp_dept에 insert문장을 수행하는 경우 기존에 존재하는 dept정보에 대해서는 update를 수행하고, 새로은 dept정보는 insert하도록 하였다. 그리고 emp table에 대해서는 empno가 primary key이므로 중복되는 row는 자동으로 오류가 발생하게 되며, 새로운 값을 insert하게 되면 emp table에 직접 insert를 하게 된다. create or replace trigger emp_dept_insert instead of insert on emp_dept referencing new as n for each row declare dept_cnt number; begin if :n.empno is not null then insert into emp(empno, ename, sal) values (:n.empno, :n.ename, :n.sal); end if; if :n.deptno is not null then select count(*) into dept_cnt from dept where deptno = :n.deptno; if dept_cnt > 0 and (:n.dname is not null) then update dept set dname = :n.dname where deptno = :n.deptno; else insert into dept(deptno, dname) values(:n.deptno, :n.dname); end if; end if; end; / (4) DML statement 다음과 같이 insert 문장을 view에 수행하면 emp_dept_insert trigger가 fire되어 실제 emp와 dept table에 반영이 되어 emp_dept view에 insert가 수행된 것 같이 보이게 된다. insert into emp_dept values (5000, 'EYKIM', 100, 10, 'SALES'); insert into emp_dept(empno, ename, deptno) values (6000, 'YOUNKIM', 20); insert into emp_dept (empno, deptno, dname) values (7000, 50, 'NEW_DEPT'); from oracle