오라클 트리거

트리거 (TRIGGER) 란?

• 간단히 말해 INSERT,UPDATE, DELETE 문이 Table 에 대하여 수행 되어질 때 묵시적으로 수행되는 Procedure 이다.
• 트리거는 table 에 저장되는 것이 아니라 별도로 오라클 데이터베이스 자체에 저장 된다.
• 트리거는 view 에 대해서는 동작하지 않고 table 자체에 대해서만 정의 될 수 있다.

 

트리거란 특정 테이블의 데이터에 변경이 가해졌을때 묵시적으로 자동으로 수행되는 저장 프로시져라 할 수 있다. C++ 언어 또는 Java 의 Class 의 경우 Constructor 와 Destructor 가 있다. 이들은 Class 가  만들어 질때 자동으로 호출되거나 아니면 소멸되기 전에 자동으로 호출되는 묵시적 수행 함수이다. 바로 이와 같은 기능이 트리거이다.

일반적으로 저장 프로시져 (Stored Procedure)는 사용자가 필요할 때 직접 수행시켜서 해당 프로시져를 수행하였다. 하지만 트리거는 이와 다르게 테이블에 INSERT, UPDATE, DELETE 문에 의하여 변경이 되어질 때 자동으로 수행되므로 이 기능을 이용하여 여러가지 작업을 할 수 있다. 트리거는 이와 같은 특성이 있기 때문에 사용자가 직접 호출 할 수는 없다.

 

 

INSERTED / DELETED 테이블

트리거를 잘 사용하기 위해서는 INSERTED 와 DELETED 라는 테이블을 이해 해야 한다. 다시 한번 읽어 보기 바란다. INSERT 와 DELETE 가 아니다. INSERTED 및 DELETED 이다. SQL의 INSERT , DELETE 구문이 아니라는 점을 알기 바란다.  이들은 둘다 테이블 이름이다.

INSERTED 테이블

DELETED 테이블

그럼 이 두가지의 테이블은 과연 무엇일까?? 이 두개의 테이블은 물리적으로 존재하는 테이블이 아니다. 그렇다면, 바로 논리적인 테이블로 그냥 임시 테이블이라 생각하면 좋을듯 하다.

특정 테이블의 데이터가 변경이 가해졌을때 트리거를 위해 자동으로 만들어지는 논리적인 가상의 테이블인 것이다. 그 이름에서 알 수 있듯이 INSERTED 테이블은 테이블에 새로운 데이터가 INSERT 될 때, DELETED 테이블은 테이블의 데이터가 삭제 될때 만들어 진다.

여기서 우리는 한가지 의문을 가진다, UPDATE 에 대한 테이블은 없는가?  하고... 이에 대한 해답은

UPDATE = DELETE + INSERT

이기 때문이다. 즉 변경된다는 것은 기존 값이 제거되고 새로운 값이 추가 되는 것으로 생각할 수 있기 때문이다.

즉 UPDATE 에 의한 테이블 변경은 INSERTED 및 DELETED 테이블이 동시에 존재 한다는 것을 의미 한다.

 

그러면 이 두개의 가상 테이블에는 어떠한 값이 존재할까?

DELETED 테이블에는 제거 되기 바로 전의 데이터 들이 있게되고 INSERED 테이블에는 변경되는 새로운 내용이 들어가게 된다.

* 이 두가지의 테이블은 말그대로 테이블이다. 그러므로 select * from DELETED ; 와 같이 사용할 수 있다는 것이다.

 

트리거 만들기

CREATE TRIGGER 문이 트리거를 만들어 주는 명령이다. 또한 DROP TRIGGER 를 이용하여 만들어진 트리거를 제거할 수도 있다. 또한 ALTER TRIGGER를 이용하여 기존의 트리거를 수정 할 수도 있다.

 

트리거를 만들때는 다음과 같은 내용을 포함하게 된다.

• 트리거의 이름
• 어느 테이블에 대한 트리거인지 테이블 이름 지정
• INSERT,DELETE,UPDATE 중 어느 경우에 수행될 트리거인지 지정 (복수 선택이 가능하다)
• 실제 수행될 쿼리문 : 이 쿼리문 안에서 INSERTED 및 DELETED 테이블을 이용할 수 있다.

 

트리거를 이용하여 어떤 작업이든 수행할 수는 없다.  트리거를 통하여 수행할 수 없는 작업들도 있다는 것이다. 수행할 수 없는 작업들은 다음과 같다.

• 데이터베이스의 변경 : ALTER DATABASE
• 데이터베이스의 생성 : CREATE DATABASE
• 데이터베이스의 제거 : DROP DATABASE
• 데이터베이스의 복구 : RESTORE DATABASE
• 로그 복구 : RESTORE LOG
• 기타(DISK INIT, DISK RESIZE, LOAD DATABASE, LOAD LOG, RECONFIGURE)

 

트리거를 어느때 사용하는가 를 살펴 보자.

• 데이터베이스 테이블 생성하는 과정에서 참조 무결성과 데이터 무결성 등의 복잡한 제약 조건 생성하는 경우
• 데이터베이스 테이블의 데이터에 생기는 작업의 감시, 보완
• 데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행하는 경우
• 불필요한 트랜잭션을 금지하기 위해
• 컬럼의 값을 자동으로 생성되도록 하는 경우
• 복잡한 뷰를 생성하는 경우

 

 

트리거의 생성 문법 구조

CREATE [ OR REPLACE ] TRIGGER 트리거이름 BEFORE | AFTER | INSTED OF

Triggering_event ON 테이블이름

[ FOR EACH ROW ]

[ WHEN (조건식) ]

PL/SQL 블록;

 

좀더 정밀한 문법 구조...........

CREATE [OR REPLACE] TRIGGER [schema.]trigger
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column [, column] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...
ON [schema.]table
[ [REFERENCING { OLD [AS] old [NEW [AS] new]
| NEW [AS] new [OLD [AS] old] } ]
FOR EACH ROW
[WHEN (condition)] ]
pl/sql_block 

트리거 요소들 설명

CREATE [OR REPLACE] TRIGGER [schema.]trigger

트리거 생성,재생성명령.
BEFORE | AFTER 

트리거의 시작시점이 트랜잭션의 전인지 후인지를 나타냄. 
{DELETE | INSERT | UPDATE [OF column [, column] ...]}

데이타의 처리유형
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

트리거의 처리유형을 조합하여 선언할때 사용하는 
ON [schema.]table

트리거가 INVOKE시킬 트랜잭션이 일어나는 테이블
FOR EACH ROW : 트리거의 종류, 

FOR EACH ROW이면 Row-Level Trigger
이문구를 쓰지 않으면 Statement-Level Trigger
[WHEN (condition)] ]

데이타의 처리유형이외의 조건을 삽입할 경우에 사용
pl/sql_block

실제 트리거의 BODY부분.

 

 

트리거를 사용하기 위한 권한들

SYSTEM PRIVILEGE
변경할 테이블의 OWNER 권한.
alter table 권한.
ALTER ANY TABLE권한
CREATE TRIGGER권한.
CREATE ANY TRIGGER권한.
ALTER ANY TRIGGER 권한.
트리거 트랜잭션을 수행할 수 있는 권한.

 

이러한 트리거의 유형은 크게 두가지로 나뉜다. : 트리거의 유형

행수준 트리거(Row -Level Triggers) : 트랜잭션내의 각 행에 대해 한 번만 수행             
문수준 트리거(Statement-level Triggers): 트랜잭션내에서 한번만 수행

 

트리거의 발생 시점

이벤트의 전후에 트리거가 발생   
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} {  row|statement}
2*3*2 총 12가지의 유형이 나올수 있다.

 

 

트리거의 삭제

DROP TRIGGER [schema.]trigger  
트리거가 데이터 딕셔너리에서 영구히 삭제된다.

트리거의 Enable / Disable

기본적으로 트리거를 생성함과 동시에 활성화가 되어 있다.      

ALTER TRIGGER [schema.]trigger
{ ENABLE | DISABLE | COMPILE }

ENABLE : 트리거를 실행 할 수 있는 상태로 만든다. 
DISABLE : 트리거를 실행하지 못하는 상태로 만든다. 
COMPILE : 트리거를 재 컴파일 한다. 
특정 테이블의 모든 트리거를 활성화, 비활성화 시킬 수 있다.
ALTER TABLE [schema.]table_name 
[ENABLE|DISABLE] ALL TRIGGERS

 

흔히 사용하는 트리거 이름 기법 (작명 기법)

트리거의 명칭은 적용될 테이블, 기동하는 DML명령, 
전후(before/after)상태, row-level인지 아닌지 명확하게 표시하는게 좋다.
ledger_def_upd_row
ledger_aft_upd_ins_row 등등.

 

트리거의 조합에 따라서 INSERTING, UPDATING, DELETING의 세가지로 비교할 수 있다.

INSERTING : 트리거링 문장이 INSERT일때 TRUE 그렇지 않으면 FALSE
UPDATING : 트리거링 문장이 UPDATE일때 TRUE 그렇지 않으면 FALSE
DELETING : 트리거링 문장이 DELETE일때 TRUE 그렇지 않으면 FALSE

 

 

 

출처: http://opensourceforge.tistory.com/272