TRIGGER 고찰

안녕하세요 박성원입입니다.

Mysql에서 최근에 도입된 기능 중 (최근이아니죠 ;; 최신버전입니다. ㅋ)
Trigger에 대해서는 사실 저는 잘 모릅니다.
대게 실무에서 다루는 mysql의 버전이 낮기 때문에 적용을 못하기 때문이죠 ^^;;

그래서 부족한 부분을 좀 매워볼까해서 이번에 trigger에 대해 정리 해보았습니다.

꽤 많은 시간을 들여서 정리해봤는데요 ;;;
막상 쓰고나니 얼마 안되는군요 내용도 많이 부족한거 같아서 부끄럽습니다.
나중에 좀 더 수정하고 예문도 만들어서 넣어야겟네요 ^^;;

■ Trigger란?

제약조건과 함께 데이터 무결성을 지키는 하나의 방법으로써 특정 이벤트에 대해서 연속(연쇄)적으로 자동 동작하는 특수한 형태의 저장 프로시저라고 볼 수 있다.

■ 데이터 무결성이란?

A. DATA 간의 결점(오류)를 발생시키지 않게 하는것..(내가 생각하기엔 ;;)

a. 종류

- 도메인 무결성 : 테이블의 각 컬럼 단위에 대해서 무결성 검증의 제약조건

(EX. 성별 컬럼에 데이터 Default등을 이용해서 방지할 수 있다)

          - 개체 무결성 : 하나의 테이블(또는 뷰)의 범위를 포함하는 제약조건

(EX. PK, Unique를 이용하여 제약을 사용한다.)

- 참조 무결성 : 부모테이블과 자식테이블의 상속관계에 대한 제약조건

(Ex. PK, FK를 이용해서 제약조건을 사용한다

Tip 1. 참조 무결성에서 PK와 연결된(부모키) FK(자식키)를 삭제할 때 문제점 발생

 

* PID PName SSN

P_Table

* CID PID CNAME SSN

C_Table

참조 무결성 제약조건은 하기와 같은 경우에 발생한다.

a. C_Talbe에 INSERT, UPDATE 시 발생

b. P_Table에 Delete 시 발생

관계 설정을 거미줄 처럼 매우 많이 설정하는 경우 하나의 부모키가 여러 자식 Table에 등록 되어있을 때
부모키의 삭제 시 자식키를 모두 수동으로 지워야하는 현상이 발생 할 수 있습니다.
이러한 경우를 해결하기 위해서
 ‘연계 참조 무결성 제약조건(or 케스케이딩 참조 무결성 제약조건)’을 사용합니다.

 

■ 2000과의 차이점

A. 2000에서는 DML에서만 동작하였으나 DDL에서 동작 할 수 있도록 확장되었음

 

■ DML이란?

   A. Data Manipulation Language(데이터 조작 언어)

   B. DB 사용자가 응용 프로그램이나 SQL을 통해 DATA를 조회 및 수정하는 언어

   (ex. SELECT, INSERT, DELETE, UPDATE 등)

■ DDL이란?

 A.  Data Definition Language(데이터 정의 언어)

 B.  DML의 선행되는 언어로써 DML을 수행하기 위해 기반이 될 수 있는 테이블, 인덱스, 저장 프로시저,
        뷰, 사용자 등의 객체를 생성하거나 이를 수정 및 삭제하는 역할을 하는 언어
  (ex - SQL : CREATE, DROP, ALTER, GRNAT, REVOKE 등)

■ DML Trigger??

A. 문법

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT] [,] [UPDATE] [,] [DELETE] } }
AS
			SQL_STATEMENT

B. 정의

  a. AFTER

    정의된 INSERT, UPDATE, DELETE문 수행 후에 작업이 수행

   b. FOR

     AFTER 와 동일함 (기존까지 사용하던 이름 이였음)

   c. INSTEAD OF

     기존에 존재하는 유형의 트리거의 작업을 완전히 무시하고 대신 수행함

         (어떠한 제약사항보다 먼저 INSTEAD OF 트리거 작업을 수행하게 된다.)

 

정리 1. 차이점??
AFTER Trigger의 경우 Event 자체를 먼저 수행한 후 SQL_STATEMENT를 수행하나 INSTEAD OF의 경우 Event를 수행하지 않고 SQL_STATEMENT를 수행한다.

 

정리 2. INSTEAD OF TRIGGER 왜 사용하는가?

Distributed Partitioned View(분산 파티션드 뷰)와 같은 스케일 아웃 기능의 Table들에서 DATA를 변경할 경우
DML을 분산된 테이블 중 어떠한 것을 선택하는지를 Check를 할 수 있도록 고안

된 것이 INSTEAD OF TRIGGER 이다.(inserted, deleted를 사용)

 

C. 조회, 변경 및 삭제

a. 조회

          - 조회 : SLEECT * FROM SYS.TRIGGERS

          - 트리거의 텍스트 : Sp_helptest trigger_name

          - 참조객체 조회 : Sp_depands trigger_name

  b. 변경        

          ALTER TRIGGER TITLES_MESSAGE

          ON TITLES

          FOR INSERT

          PRINT ‘성공’       

   c. 삭제

          DROP TRIGGER TITLES_MESSAGE

D. 트리거 순서 지정하기

  여러 개의 Trigger가 중첩되어있는 경우 트리거의 순서를 지정 해야 한다.

  순서는 처음과 끝만을 지정하며 중간의 단계는 임의로 수행된다

Sp_settriggerorder [@triggername=] ‘[triggerschema.] triggername’
                   , [@order=] ‘values’
	        , [@stmttype=] ‘statement_type’
	        [ , [@namespace=] {‘DATABASE’ | ‘SERVER’ | NULL } ]

EX.)
Sp_settriggerorder ’trigger-1’, ’frist’, ’INSERT’
Sp_settriggerorder ’trigger-2’, ’last’, ’INSERT’

E. Trigger 사용 및 정지

  a. 트리거 사용 (전체)

          ALTER TABLE TABLE_NAME

          ENABLE TRIGGER ALL

b. 트리거 사용 (지정)

          ALTER TABLE TABLE_NAME

          ENABLE TRIGGER TRIGGER_NAME

c. 트리거 사용안함 (전체)

          ALTER TABLE TABLE_NAME

          DISABLE TRIGGER ALL

d. 트리거 사용안함 (지정)

          ALTER TABLE TABLE_NAME

          DISABLE TRIGGER TRIGGER_NAME

  F. 참고 사항

a. Trigger는 부모키와 자식키의 무결성 제약을 위반 할 경우 실행되지 않는다.

  (이를 INSTEAD OF를 이용해서 Check 해서 사용할 수 있다.)

b. inserted, deleted 테이블

- 트리거가 사용하는 논리적 테이블

       - 인덱스가 없음(query를 수행할때마다 테이블 전체를 스켄함)

c. MSSQL server 2000에서의 inserted, deleted 테이블

       - 트리거에 의해서 실행되는 문장에 대한 로그 레코드를 포함하는 트랜젝션 로그의 일부로
                 만들어진 사실상의 뷰

       - 테이블을 쿼리할 때마다 트랜젝션 로그의 일부를 스캔하면서 병목현상을 유발시킨다. 
                 또한 트렌젝션 로그를 순차적으로 기록하는 작업에 방해가 생겨 변경된 데이터 디스크에
                 기록하는 작업을 지연시킨다.

d. MSSQL server 2005에서의 inserted, deleted 테이블

             - tempdb에 있는 행 버전이 관리되는 데이터를 가리킨다.           

             - 트랜젝션 로그의 일부가 아님(tempdb로 부하가 발생함)

             - 트리거의 수행이 많을 경우 성능상 문제가 발생함

■ DDL Trigger??

A. 문법

  	CREATE TRIGGER TIRGGER_NAME
	ON { ALL SERVER | DATABASE }
	[WITH ENCRYPTION]
	{ FOR | AFTER } { EVENT_TYPE | EVENT_GROUP }
	As
	SQL;

B. 정의

          a. ALL SERVER

           모든 서버 범위에서 DDL문의 이벤트에 대한 트리거를 생성

           (감사작업, 스키마변경 금지, 새로운 로그인 계정 생성금지 등으로 활용)

          b. DATABASE

            DATABASE 범위 내에서 트리거를 생성

            (새로운 테이블 생성시 로그 저장 등에 활용)

          c. FOR | AFTER

               트리거의 종류는 FOR(or AFTER) 한종류만 사용가능 함

정리. 트리거링 이벤트가 발생할 때, 누가 언제 어떤 프로그램등이 어떠한 명령어를 사용한 정보들이
XML형태로 전달된다. 이 XML값이 EVENTDATA() 함수의 반환 값이고, 맵핑 정보를 이용해서 세부
값들을 가져올 수 있다.
(세부 값 : 이벤트 형식, 호출된시간, 세션의 서버 프로세스 ID, 인스턴스이름, 로그인 이름, 사용자이름,
데이터베이스 이름, 개체의 스키마 이름, 개체 이름, 개체 형식, SET 옵션의 상태, 트리거를 호출한 실제
T-SQL 문장 등)

세부 값은 XQuery를 이용하여 xml에서 데이터를 추출하면 된다.

■ Trigger 사용시 주의사항

             a. 임시 테이블에는 트리거를 생성할 수 없다.

             b. 하나의 테이블에 여러 트리거를 사용하는 경우 순서 정하기가 까다롭고 관리하기 힘들다.

             c. DELETE와 TRUNCATE는 다른 이벤트이므로, DELETE 트리거를 걸어도 TRUNCAT 문에서는
동작 하지 않는다.

             d. 한문장에 하나씩 수행되므로 insert select 같은 벌크 복사 문에서도 한번만 수행한다.
(커서나 임시테이블을 이용해서 구현할 수 있다.)

참고자료

1.       SQL server 2005 완벽가이드

2.       http://www.volkit.com/505