PL /SQL) TRIGGER(트리거)

<TRIGGER(트리거)>

데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다. 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다.

트리거에는 크게 나누어 행 트리거와 문장 트리거의 두 종류가 있다.

행 트리거: 테이블 안의 영향을 받은 행 각각에 대해 실행된다. 변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다.

문장 트리거:INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다.

또한 트리거는 다음과 같은 속성을 갖는다.

BEFORE 또는 AFTER

트리거가 실행되는 시기를 지정한다.

INSTEAD OF

트리거를 원래 문장 대신 수행한다.

WHEN

트리거를 시작하는 조건식을 지정한다.

일반적으로 트리거는 다음의 3 가지 경우에 시작된다. 트리거는 SELECT 문에 의한 데이터 검색에 영향을 미칠 수 없다.

INSERT (새로운 행 삽입)

UPDATE (기존 행의 변경) / UPDATE OF (기존 행의 특정 열 변경)

DELETE (기존 행 삭제)

출처:https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%ED%8A%B8%EB%A6%AC%EA%B1%B0

<트리거 기본>

-- 트리거 생성 예제

create or replace trigger tri_01

after insert on tri_emp

BEGIN -- 자동 동작할 내용

DBMS_OUTPUT.PUT_LINE('신입사원 입사');

END;

create or replace trigger tri_02

after update on tri_emp

BEGIN

DBMS_OUTPUT.PUT_LINE('신입사원 수정');

END;

create or replace trigger tri_03

after delete on tri_emp

BEGIN

DBMS_OUTPUT.PUT_LINE('신입사원 삭제');

END;

--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 '09:00' and '16:00') THEN

RAISE_APPLICATION_ERROR(-20002, '허용시간 오류 쉬세요');

END IF;

END;

<for each row>

-- for each row 예제

create table emp2

as

select * from emp;

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;

-- for each row (O) vs for each row (X)

-- for each row (O) : 행 단위로 트리거를 거는 것.

-- for each row (X) : 쿼리 명령문 단위로 트리거를 거는 것.

-- 예시 : 한 번의 쿼리로 update row가 3건 걸렸다

-- 결과 : for each row (O) => 트리거 3번 발동

-- for eqch row (X) => 트리거 1번 발동

<NEW, OLD 활용>

--PL_SQL 두개의 가상데이터(테이블) 제공

--:OLD > 트리거가 처리한 레코드의 원래 값을 저장(ms-sql (deleted)

--:NEW > 넣을 데이터를 미리 모아둔 테이블(ms-sql (inserted)

create or replace trigger tri_order2

before insert on tri_order

for each row

BEGIN

IF(:NEW.ord_code) not in('desktop') THEN

RAISE_APPLICATION_ERROR(-20002, '제품코드 오류');

END IF;

END;

--INSERT, UPDATE, DELETE로 변경되는 내용에 대하여 전/후 데이터를 기록한다.

create table emp_audit (

id number(6) constraint emp_audit_pk primary key,

name varchar2(30),

gubun varchar2(10),

wdate date,

etc1 varchar2(20), -- old

etc2 varchar2(20) -- new

);

-- :old.deptno, :new.deptno가 핵심이다

-- 보험용 성격으로 보관을 해놓을 일이 있으면 사용하면 될 듯 하다

-- delete의 경우 old 데이터만 잡힌다

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;

--입고 데이터 들어오면 같은 데이터를 재고 입력

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;

-- 입고 제품이 변경 (재고 변경)

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;

-- 여기서 지우면 저기서도 지우도록

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;