Oracle Trigger (오라클 트리거)

11.1 개요

TRIGGER란 DML 작업 즉, INSERT, DELETE, UPDATE 작업이 일어날 때 자동으로 실행되는 객체로 특히 이런 TRIGGER를 DML TRIGGER라 한다. TRIGGER는 데이터의 무결성 뿐만 아니라 다음과 같은 작업에도 사용된다.

⋅자동으로 파생된 열 값 생성

⋅잘못된 트랜잭션 방지

⋅복잡한 보안 권한 강제 수행

⋅분산 데이터베이스의 노드 상에서 참조 무결성 강제 수행

⋅복잡한 업무 규칙 강제 수행

⋅투명한 이벤트 로깅 제공

⋅복잡한 감사 제공

⋅동기 테이블 복제 유지 관리

⋅테이블 액세스 통계 수집

￭ 주의

트리거 내에서는 COMMIT, ROLLBACK 문을 사용할 수 없다.

11.2 TRIGGER의 종류

트리거는 SQL문이 언제 실행되느냐에 따라 또는 트리거하는 SQL 문장에 의해 영향 받는 각 row에 대해 트리거가 실행되느냐 아니냐에 따라 다음과 같이 분류된다.

SQL문의 실행시기에 따른 분류	BEFORE 트리거	SQL 문장이 실행되기 전 트리거가 먼저 실행됨
SQL문의 실행시기에 따른 분류	AFTER 트리거	SQL 문장이 실행된 다음 트리거가 실행됨
SQL문에 의해 영향 받는 각 row에 따른 분류	ROW 트리거	SQL 문장의 각 row에 대해 한번 씩 실행
SQL문에 의해 영향 받는 각 row에 따른 분류	STATEMENT 트리거	SQL 문장에 대해 한번만 실행 (DEFAULT TRIGGER)

실행 시점과 범위를 조합하여 트리거는 다음 4가지로 분류한다.

실행시점	실행범위	내용
BEFORE	STATEMENT	SQL 문이 실행되기 전에 그 문장에 대해 한번 실행
BEFORE	ROW	DML 작업하기 전에 각 ROW에 대해 한 번씩 실행
AFTER	STATEMENT	SQL 문이 실행된 후 그 문장에 대해 한번 실행
AFTER	ROW	DML 작업한 후 각 ROW에 대해 한 번씩 실행

⋅문장 트리거

트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오진 한번만 트리거를 발생시키는 방법

예를 들어 "UPDETE emp SET 급여 = 급여 * 1.1;" 문장이 실행되면 여러 행에 대하여 자료가 변경 되더라도 한번만 트리거가 실행된다.

⋅행 트리거

조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건]절 정의된다.

11.3 TRIGGER의 생성

￭ 형식

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

이벤트-1 [OR 이벤트-2 OR 이벤트-3] ON 테이블명

[FOR EACH ROW [WHEN TRIGGER 조건]]

DECLARE

선언문

BEGIN

PL/SQL 코드

END;

⋅이벤트

INSERT, UPDATE, DELETE

⋅BEFORE : 구문을 실행하기 전에 트리거를 시작

⋅AFTER : 구문을 실행한 후에 트리거를 시작

⋅FOR EACH ROW : 행 트리거임을 알림

⋅WHEN 조건 : 사용자의 트리거 이벤트 중에 조건에 만족하는 데이터만 트리거 한다.

⋅REFERENCING : 영향 받는 행의 값을 참조

⋅:OLD : 참조 전 열의 값(INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)

⋅:NEW : 참조 후 열의 값(INSERT : 입력할 자료, UPDATE : 수정할 자료)

ROW 트리거에서 컬럼의 실제 데이터 값을 제어하는데 사용하는 연산자는 :OLD와 :NEW이다. 이 연산자와 함께 컬럼 명을 함께 기술한다. 예를 들어, 컬럼명이 sal이라고 하면, 변경전의 값은 :OLD.sal이고 변경 후의 값은 :NEW.sal 처럼 표기한다.

또한 문장 트리거에서는 :NEW, :OLD 를 참조 할 수 없다.

11.4 TRIGGER의 상태 확인

트리거 정보를 확인하기 위하여 dba_triggers 뷰, dba_trigger_cols 뷰, user_triggers 뷰를 사용하여 확인할 수 있다.

￭ 사용 예

SQL>SELECT trigger_name, trigger_type, table_name FROM user_triggers;

11.5 TRIGGER의 상태 변경 및 재 컴파일

[1] 상태 변경

￭ 형식

ALTER TRIGGER 트리거명 {ENABLE | DISABLE};

위와 같은 문을 사용하여 트리거를 활성화/비활성화로 전환할 수 있다. 또한 ALTER TABLE 테이블명 {DISABLE | ENABLE} ALL TRIGGERS; 문을 사용하여 해당 테이블에 있는 모든 트리거를 비활성화/활성화를 동시에 실행할 수 있다.

[2] 재 컴파일

￭ 형식

ALTER TRIGGER 트리거명 COMPILE;

트리거의 내용이 변경되었거나, 트리거가 제대로 동작하지 않을 때 사용자가 직접 재 컴파일 해서 사용 가능 상태로 만들 수 있다.

11.6 TRIGGER의 삭제

￭ 형식

DROP TRIGGER 트리거명;

11.7 예제

11.7.1 문장 트리거 실습

￭ 권한 부여

-- scott 사용자에게 트리거를 만들 수 있는 권한 부여

CMD>sqlplus sys/암호 as sysdba

SQL>GRANT CREATE TRIGGER TO scott;

-- 부여된 권한 확인

SQL>CONN scott/tiger

SQL>SELECT * FROM USER_SYS_PRIVS;

-- 서버에 있는 모든 시스템 권한을 보임

SQL>SELECT * FROM system_privilege_map;

￭ DML문장에 의해 데이터가 변경된 시간을 저장하는 트리거 작성(AFTER 트리거)

SQL>CREATE TABLE ExamData(

id NUMBER

,name VARCHAR2(20)

);

SQL>CREATE TABLE ExamMemo(

memo VARCHAR2(20)

,ilja DATE DEFAULT SYSDATE

);

SQL>CREATE OR REPLACE TRIGGER trg_ExamData

AFTER DELETE OR INSERT OR UPDATE ON ExamData

BEGIN

-- 삽입할 때

IF INSERTING THEN

INSERT INTO ExamMemo(memo) VALUES ('insert');

-- 수정할 때

ELSIF UPDATING THEN

INSERT INTO ExamMemo(memo) VALUES ('update');

-- 삭제할 때

ELSIF DELETING THEN

INSERT INTO ExamMemo(memo) VALUES ('delete');

END IF;

END;

SQL>SELECT trigger_name, trigger_type, table_name FROM user_triggers;

SQL>INSERT INTO ExamData(id, name) VALUES (1, 'aaaa');

SQL>INSERT INTO ExamData(id, name) VALUES (2, 'bbbb');

SQL>COMMIT;

SQL>DELETE FROM ExamData WHERE id = 1;

SQL>COMMIT;

SQL>SELECT * FROM ExamMemo;

SQL>DROP TRIGGER trg_ExamData;

SQL>DROP TABLE ExamData PURGE;

SQL>DROP TABLE ExamMemo PURGE;

￭ DML문장에 대하여 평일 7~17시 사이에만 자료를 변경도록 트리거 작성 - 보안(BEFORE 트리거)

⋅통화기호, 날짜 등 출력 형식 확인

SELECT parameter, value FROM NLS_SESSION_PARAMETERS;

⋅통화기호, 날짜 등 출력 형식 변경

ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';

ALTER SESSION SET NLS_CURRENCY = '\';

ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';

⋅트리거 작성

SQL>CREATE OR REPLACE TRIGGER insa_trigger1

BEFORE DELETE OR INSERT OR UPDATE ON insa

BEGIN

IF TO_CHAR(SYSDATE, 'DAY') IN ('토요일', '일요일') OR

TO_CHAR(SYSDATE, 'hh24') < 7 OR TO_CHAR(SYSDATE, 'hh24') > 17 THEN

raise_application_error(-20007, '평일 7~17시에만 작업 가능');

END IF;

END;

SQL>SELECT basicPay FROM insa WHERE num = 1060;

SQL>UPDATE insa SET basicPay = 1500000 WHERE num = 1060;

SQL>COMMIT;

SQL>SELECT basicPay FROM insa WHERE num = 1060;

SQL>DROP TRIGGER insa_trigger1

raise_application_error('에러메시지', '에러번호') 메서드는 사용자 정의 예외를 보고하는 메서드로서 에러번호는 -20000 .. -20999 사이의 값을 넣어준다.

위 예제에서 raise_application_error() 함수는 에러 메시지를 출력 후에 UPDATE문을 실행하지 않지만 DBMS_OUTPUT.PUT_LINE() 를 이용하여 메시지를 출력한 경우에는 UPDATE문을 실행 한다.

11.7.2 행 트리거 예제

[1] 테이블 작성

다음과 같이 두 개의 테이블을 작성 한다.

1) 테이블 명 : examA

필드명	필드형식	NULL	CONSTRAINT	기타	설명
sa_id	VARCHAR2(5)	NOT NULL	PRIMARY KEY
name	VARCHAR2(20)	NOT NULL
basicPay	NUMBER	NOT NULL
sudang	NUMBER	NOT NULL

CREATE TABLE examA (

sa_id VARCHAR2(5) NOT NULL PRIMARY KEY,

name VARCHAR2(20) NOT NULL,

basicPay NUMBER NOT NULL,

sudang NUMBER NOT NULL

);

2) 테이블 명 : examB

필드명	필드형식	NULL	CONSTRAINT	기타	설명
sa_id	VARCHAR2(5)	NOT NULL	PRIMARY KEY examA 테이블 sa_id의 FOREIGN KEY
totPay	NUMBER
tax	NUMBER
silPay	NUMBER

CREATE TABLE examB (

sa_id VARCHAR(5) NOT NULL PRIMARY KEY

,totPay NUMBER

,tax NUMBER

,silPay NUMBER

,CONSTRAINT FK_examB_id FOREIGN KEY(sa_id) REFERENCES examA(sa_id)

);

[2] 프로시저 작성

1) examA 테이블에 자료 추가 프로시저(프로시저 이름 : procIns_examA)

CREATE OR REPLACE PROCEDURE procIns_examA

(

psa_id IN VARCHAR2, pname IN VARCHAR2, pbasicPay IN NUMBER, psudang IN NUMBER

)

BEGIN

INSERT INTO examA (sa_id, name, basicPay, sudang) VALUES

(psa_id, pname, pbasicPay, psudang);

COMMIT;

END;

2) examA 테이블 자료 수정 프로시저(프로시저 이름 : procUp_examA)

CREATE OR REPLACE PROCEDURE procUp_examA

(

psa_id IN VARCHAR2, pname IN VARCHAR2, pbasicPay IN NUMBER, psudang IN NUMBER

)

BEGIN

UPDATE examA SET name=pname, basicPay=pbasicPay, sudang=psudang

WHERE sa_id = psa_id;

COMMIT;

END;

[3] 트리거 작성

1) examA 테이블에 자료가 추가 되면 examB 테이블에 다음의 처리 조건에 따라 자료를 추가하는 트리거를 작성 한다.(트리거명 : trgIns_examB)

⋅totPay = basicPay + sudang

⋅tax 는 totPay 가 2000000 이상이면 3%, 1500000 이상이면 1.5%, 그렇지 않으면 0으로 설정하며 일의자리에서 반올림 한다.

CREATE OR REPLACE TRIGGER trgIns_examB

AFTER INSERT ON examA

FOR EACH ROW

DECLARE

vTotPay NUMBER;

vTax NUMBER;

vSilPay NUMBER;

BEGIN

vTotPay := :NEW.basicPay + :NEW.sudang;

IF vTotPay >= 2000000 THEN

vTax := ROUND(vTotPay * 0.03, -1);

ELSIF vTotPay > = 1500000 THEN

vTax := ROUND(vTotPay * 0.015, -1);

ELSE

vTax := 0;

END IF;

vSilPay := vTotPay - vTax;

INSERT INTO examB (sa_id, totPay, tax, silPay) VALUES

(:NEW.sa_id, vTotPay, vTax, vSilPay);

END;

2) examA 테이블의 자료가 수정 되면 examB 테이블의 자료가 수정되도록 트리거를 작성 한다.(트리거명 : trgUp_examB)

CREATE OR REPLACE TRIGGER trgUp_examB

AFTER UPDATE ON examA

FOR EACH ROW

DECLARE

vTotPay NUMBER;

vTax NUMBER;

vSilPay NUMBER;

BEGIN

vTotPay := :NEW.basicPay + :NEW.sudang;

IF vTotPay >= 2000000 THEN

vTax := ROUND(vTotPay * 0.03, -1);

ELSIF vTotPay > = 1500000 THEN

vTax := ROUND(vTotPay * 0.015, -1);

ELSE

vTax := 0;

END IF;

vSilPay := vTotPay - vTax;

UPDATE examB SET totPay = vTotPay, tax = vTax, silPay = vSilPay

WHERE sa_id = :NEW.sa_id;

END;

-- 1) 번과 2)번 트리거를 하나의 트리거(trg_examB)로 작성하고 1)번과 2)번 트리거를 삭제해 본다.

[4] 자료 추가 및 수정 테스트

--자료 추가

EXEC procIns_examA ('a-100', '홍길동', 1500000, 200000);

EXEC procIns_examA ('a-101', '이기자', 1200000, 100000);

EXEC procIns_examA ('a-102', '이순신', 1700000, 600000);

SELECT * FROM examA;

SELECT * FROM examB;

-- 자료 수정

EXEC procUp_examA ('a-100', '홍길동', 2000000, 200000);

SELECT * FROM examA;

SELECT * FROM examB;

[5] 자료 삭제

examA 테이블의 자료를 삭제하면 examB 테이블의 자료를 먼저 삭제하고 examA 테이블의 자료가 삭제되도록 BEFORE 트리거를 작성한다.(트리거 명 : trgDel_examB)

-- 트리거 작성

CREATE OR REPLACE TRIGGER trgDel_examB

BEFORE DELETE ON examA

FOR EACH ROW

BEGIN

DELETE FROM examB WHERE sa_id = :OLD.sa_id;

END;

-- 자료 삭제

DELETE FROM examA WHERE sa_id = 'a-101';

COMMIT;

-- 확인

SELECT * FROM examA;

SELECT * FROM examB;

[6] 트리거 삭제

1) procIns_examA, procUp_examA프로시저 삭제

DROP PROCEDURE procIns_examA;

DROP PROCEDURE procUp_examA;

2) trgIns_examB, trgUp_examB, trgDel_examB 트리거 삭제

DROP TRIGGER trgIns_examB;

DROP TRIGGER trgUp_examB;

DROP TRIGGER trgDel_examB;

3) examB, examA 테이블 삭제

DROP TABLE examB PURGE;

DROP TABLE examA PURGE;

11.7.3 행 트리거 실습

1. 테이블 작성

1) [상품] 테이블

필드명	필드형식	NULL	CONSTRAINT	기타	설명
상품코드	가변 문자열(6)	not null	primary key
상품명	가변 문자열(30)	not null
제조사	가변 문자열(30)	not null
소비자가격	정수형
재고수량	정수형		default 0

2) [입고] 테이블

필드명	필드형식	NULL	CONSTRAINT	기타	설명
입고번호	정수형		primary key
상품코드	가변 문자열(6)		[상품] 테이블 [상품코드]의 foreign key
입고일자	날짜형
입고수량	정수형
입고단가	정수형

3) [판매] 테이블

필드명	필드형식	NULL	CONSTRAINT	기타	설명
판매번호	정수형		primary key
상품코드	가변 문자열(6)		[상품] 테이블 [상품코드]의 foreign key
판매일자	날짜형
판매수량	정수
판매단가	정수

-- 상품 테이블 작성

CREATE TABLE 상품 (

상품코드 VARCHAR2(6) NOT NULL PRIMARY KEY

,상품명 VARCHAR2(30) NOT NULL

,제조사 VARCHAR2(30) NOT NULL

,소비자가격 NUMBER

,재고수량 NUMBER DEFAULT 0

);

-- 입고 테이블 작성

CREATE TABLE 입고 (

입고번호 NUMBER PRIMARY KEY

,상품코드 VARCHAR2(6) NOT NULL CONSTRAINT FK_ibgo_no

REFERENCES 상품(상품코드)

,입고일자 DATE

,입고수량 NUMBER

,입고단가 NUMBER

);

-- 판매 테이블 작성

CREATE TABLE 판매 (

판매번호 NUMBER PRIMARY KEY

,상품코드 VARCHAR2(6) NOT NULL CONSTRAINT FK_pan_no

REFERENCES 상품(상품코드)

,판매일자 DATE

,판매수량 NUMBER

,판매단가 NUMBER

);

2. 상품 테이블에 자료 추가