[DB] Trigger

Trigger

1) 정의 : 이벤트에 의해 자동으로 호출되어 실행되는 프로시저

2) 문법

CREATE [OR REPLACE] TRIGGER 트리거명 {BEFORE | AFTER}

트리거 이벤트 ON 테이블명 [반복문]

BEGIN

END;

3) DD : user_triggers

4) 트리거는 기본적으로 2개의 임시테이블을 가지고 있다.

NEW(:new), OLD(:old)

Trigger TEST ▼

1) emp테이블에서 급여를 수정할 때 현재의 값보다 적게 수정할 수 없고 현재의 값보다 10%이상 높게 수정할 수 없도록 제한하는 트리거 작성

CREATE OR REPLACE TRIGGER tri_emp_update

BEFORE UPDATE ON emp FOR EACH ROW 

WHEN(NEW.sal<OLD.sal OR NEW.sal>=OLD.sal*1.1)

BEGIN

raise_application_error(-20506, '수정된 값이 잘못되었다.');

END;

UPDATE emp

SET sal=2000

WHERE empno=7788;

DROP TRIGGER tri_emp_update;

2) emp테이블을 사용할 수 있는 시간은 월요일부터 금요일까지이며 09시부터 18시까지만 사용할 수 있또록 하는 트리거

CREATE OR REPLACE TRIGGER tri_resource

BEFORE UPDATE OR INSERT OR DELETE ON emp

BEGIN

IF 

to_char(sysdate, 'dy') in ('토', '일', '화')

OR

to_number(to_char(sysdate, 'HH24')) NOT BETWEEN 9 AND 18

THEN

raise_application_error(-20506, '작업시간이 아닙니다.');

END IF;

END;

DROP TRIGGER tri_resource;

3) emp테이블에 insert, update, delete문장이 하루에 몇건이나 발생하는지 조사하려고 한다.

   조사내용은 emp_audit라는 테이블에 저장하도록 한다.

   조사항목은 사용자이름, 작업구분, 작업시간으로 처리한다.

CREATE TABLE emp_audit(

e_id            number(5),

e_name       varchar2(30),

e_gubun      varchar2(10),

e_date        date,

constraint pk_eid primary key(e_id)

);

CREATE OR REPLACE TRIGGER tri_emp_audit

AFTER UPDATE OR INSERT OR DELETE ON emp

BEGIN

IF inserting THEN

INSERT INTO emp_audit VALUES(seq_empno.nextval, user, 'insert', sysdate);

ELSIF updating THEN

INSERT INTO emp_audit VALUES(seq_empno.nextval, user, 'update', sysdate);

ELSIF deleting THEN

INSERT INTO emp_audit VALUES(seq_empno.nextval, user, 'delete', sysdate);

END IF;

END;

DROP TRIGGER tri_emp_audit;

DROP table emp_audit;

update emp

set sal=3000

where ename='홍길동';

delete from emp where name='임꺽정';

select*from emp_audit;

3) 재고 수량 관리 트리거

- 테이블 설계(입고, 출고, 재고)

컬럼명        num        name          cnt

------------------------------------------------

key type       pk

sequence      1,1

null/unique                  nn             nn

data type   number   varchar2(30)   number

- 입고 테이블에 데이터를 입력한다.

  자동으로 동시에 재고테이블에 데이터가 입력된다.

  만약 재고 테이블에 이미 동일한 데이터가 있으면 물건의 갯수만 증가시킨다.

- 출고 테이블에 데이터를 입력한다.

  자동으로 동시에 재고테이블의 수량이 감소한다.

  만약 재고테이블의 수량이 출고할 데이터의 수량보다 적으면 "출고할 수 없습니다."라는

  메시지가 출력되고 출고테이블에 수정을 중단한다.