[DB] Trigger
- DB
- 2017. 11. 21. 14:49
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
- 입고 테이블에 데이터를 입력한다.
자동으로 동시에 재고테이블에 데이터가 입력된다.
만약 재고 테이블에 이미 동일한 데이터가 있으면 물건의 갯수만 증가시킨다.
- 출고 테이블에 데이터를 입력한다.
자동으로 동시에 재고테이블의 수량이 감소한다.
만약 재고테이블의 수량이 출고할 데이터의 수량보다 적으면 "출고할 수 없습니다."라는
메시지가 출력되고 출고테이블에 수정을 중단한다.
'DB' 카테고리의 다른 글
[DB] ERD 그려보기 예제 (0) | 2017.11.22 |
---|---|
[DB] DB 모델링 (0) | 2017.11.22 |
[DB] Procedure (0) | 2017.11.20 |
[Oracle] 오라클11g 다운로드 및 설치 (0) | 2017.11.20 |
[DB] SQL 사용법 (0) | 2017.11.20 |