[20180517] PL SQL Trigger

PL/SQL에서의 트리거 역시 방아쇠가 당겨지면 자동으로 총알이 발사되듯이 어떠한 이벤트가 발생하면 그에 따라 다른 작업이 자동으로 처리되는 것을 의미한다. 트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 [저장 프로시저]라고 할 수 있다. 앞서 배운 저장 프로시저는 필요할 때마다 사용자가 직접 EXECUTE 명령어로 호출해야 했다. 하지만 트리거는 이와 달리 테이블의 데이터가 INSERT, UPDATE, DELETE 문에 의해 변경되어질 때 [ 자동으로 수행되므로 이 기능을 이용하며 여러 가지 작업 ] 을 할 수 있다. 이런 이유로 트리거를 사용자가 직접 실행시킬 수는 없다.

BEFORE : 테이블에서 DML 실행되기 전에 트리거가 동작

AFTER :  테이블에서 DML 실행후에 트리거 동작

기본 Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
 
{BEFORE | AFTER} triggering_event [OF column1, . . .] ON table_name
 
[FOR EACH ROW [WHEN trigger_condition]
 
trigger_body;
 
 

---

활용예시 

create table tri_order
(
  no number,
  ord_code varchar2(10),
  ord_date date
);
 
--before 트리거의 동작시점이 실제 tri_order 테이블 insert 되기 전에
--트리거 먼저 동작 그 이후 insert 작업
create or replace trigger trigger_order
before insert on tri_order
BEGIN
  IF(to_char(sysdate,'HH24:MM') not between '11:00' and '16:00') THEN
     RAISE_APPLICATION_ERROR(-20002, '허용시간 오류 쉬세요');
  END IF;
END;
 
insert into tri_order values(1,'notebook',sysdate);
select * from tri_order;
commit;

트리거의 삭제

drop trigger trigger_order;

for each row 

trigger 가 작동할 때 타겟이 되는 이벤트(insert, update, delete)가 여러 행에 적용될 때, 그 행의 수 만큼 trigger가 발동되도록 하는 명령어이다. 다음의 예시를 보자.

create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row
begin
if inserting then
     insert into emp_audit
     values(emp_audit_tr.nextval, user, 'inserting', sysdate);
elsif updating then
     insert into emp_audit
     values(emp_audit_tr.nextval, user, 'updating', sysdate);
elsif deleting then
     insert into emp_audit
     values(emp_audit_tr.nextval, user, 'deleting', sysdate);
end if;
end;
 

 예를들어 delete명령어를 활용해 3건의 데이터를 삭제했다면, for each row에 의해 총 3번 emp_audit 에 총 3건의 데이터가 insert된다. 

:OLD & :NEW

create or replace trigger emp_audit_tr
after insert or update or delete on emp2
for each row
begin
if inserting then
   insert into emp_audit
   values(emp_audit_tr.nextval, user, 'inserting', sysdate, :old.deptno, :new.deptno);
elsif updating then
   insert into emp_audit
   values(emp_audit_tr.nextval, user, 'updating', sysdate, :old.deptno, :new.deptno);
elsif deleting then
   insert into emp_audit
   values(emp_audit_tr.nextval, user, 'deleting', sysdate, :old.deptno, :new.deptno);
end if;
end;

이 경우 나오는 emp_audit에 삽입되는 데이터는 각각 다음과 같다.

insert       BITUSER inserting 18/05/17 null 100

update     BITUSER updating 18/05/17 100 200

delete      BITUSER deleting 18/05/17 200 null

---

trigger를 활용한 재고관리

--입고 , 재고
 
create table t_01 --입고
(
  no number,
  pname varchar2(20)
);
 
create table t_02 --재고
(
  no number,
  pname varchar2(20)
);
 
--입고 데이터 들어오면 같은 데이터를 재고 입력
create or replace trigger insert_t_01
after insert on t_01
for each row
BEGIN
  insert into t_02(no, pname)
  values(:NEW.no ,:NEW.pname);
END;
 
--입고
insert into t_01 values(1,'notebook');
 
select * from t_01;
select * from t_02;
 
-- 입고 제품이 변경 (재고 변경)
create or replace trigger update_t_01
after update on t_01
for each row
BEGIN
  update t_02
  set pname = :NEW.pname
  where no = :OLD.no;
END;
 
update t_01
set pname = 'notebook2'
where no = 1;
 
select * from t_01;
 
select * from t_02;
 
--delete 트리거 만들어 보세요 
--입고 데이터 delete from t_01 where no =1 삭제 되면 재고 삭제
create or replace trigger delete_tri_01
after delete on t_01
for each row
BEGIN
  delete from t_02
  where no=:OLD.no;
END;
 
delete from t_01 where no=1;
 
select* from t_01;
select* from t_02;
 
commit