087 - [Oracle PL/SQL] Trigger - Instead of triggers (on Views)

뷰에 삽입한 데이터를 다르게 처리하는 방법에 대해서 알아보겠습니다.

 

 

 

• 테스트 준비

-- 테스트 준비(뷰 생성).
DROP VIEW emp_all_v;
/
create or replace view emp_all_v
as select * from employees;
/
select * from emp_all_v;

 

 

 

• 데이터 추가

-- 테스트 데이터 입력.
-- 뷰에 데이터를 삽입하면 원본 테이블에도 추가된다. 단, 단일테이블인 경우 등, 몇가지 조건하에서만 가능.
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);

 

 

 

• 원본 테이블 확인 - 뷰에 추가한 데이터가 원본 테이블에서 추가된것을 확인

-- 원본 테이블에 데이터가 삽입된 것을 확인.
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY 
from employees
where employee_id=10;
----------------------------------------------------------
EMPLOYEE_ID FIRST_NAME    LAST_NAME    HIRE_DATE JOB_ID         SALARY
----------- ------------- ------------ --------- ---------- ----------
         10 OMAR          AHMED        30-APR-24 AD_PRES          1500



rollback;-- 삽입 취소

 

 

 

• 트리거 생성 with "instead of" 옵션

-- 뷰에 데이터가 추가시 동작하는 트리거 생성
create or replace trigger TEST_INSTEAD
instead of -- 옵션을 추가함.
insert 
on emp_all_v
begin
    DBMS_OUTPUT.PUT_LINE('TEST');
end;

 

 

 

• 뷰에 데이터 추가 - 추가된것 처럼 보이지만 원본 테이블에는 추가되지 않는다

-- 테스트용 데이터를 뷰에 추가한다. 하지만 트리거 때문에 원본테이블에 데이터가 추가되지 않는다
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);
-----------------------------------------------
TEST
1 row inserted.

 

 

 

• 원본 테이블 확인 - 데이터 추가 없음

-- 데이터가 추가되지 않은것을 확인
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY 
from employees
where employee_id=10;
-----------------------------------------------
no rows selected



rollback;

 

 

 

• 트리거에 수동으로 데이터 추가하는 코드 추가

-- 추가된 데이터를 수동으로 추가하는 코드를 트리거에 구현
create or replace trigger TEST_INSTEAD
instead of
insert 
on emp_all_v
begin
 
 INSERT INTO employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.PHONE_NUMBER,:new.HIRE_DATE,:new.JOB_ID,
:new.SALARY,:new.COMMISSION_PCT,:new.MANAGER_ID,:new.DEPARTMENT_ID
);
 
DBMS_OUTPUT.PUT_LINE('TEST');

end;

 

 

 

• 데이터 추가 및 원본 테이블 확인

-- 'TEST' 문자열이 출력됨과 동시에 원본 테이블에도 데이터가 추가된다
INSERT INTO emp_all_v
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(10,'OMAR','AHMED','TEST@GMAIL.COM',null,sysdate,'AD_PRES',
1500,0,null,90
);


-- 원본 테이블에 데이터가 추가된 것을 확인
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY 
from employees
where employee_id=10;
-----------------------------------------------
EMPLOYEE_ID FIRST_NAME    LAST_NAME    HIRE_DATE JOB_ID         SALARY
----------- ------------- ------------ --------- ---------- ----------
         10 OMAR          AHMED        30-APR-24 AD_PRES          1500