*본 포스팅은 개인의 학습 내용을 정리한 것입니다.
- 트리거 : 특정 테이블에 자료가 수정될 때, 내부적으로 관련된 다른 테이블이나 컬럼에 연동하여 갱신하거나 무결성을 지키기 위해 사용된다.
1. SQL Server의 트리거
- 트리거의 유형은 세가지로 INSERT, UPDATE, DELETE트리거를 만들 수 있다. 각 트리거는 각 작업이 수행된 셋 기준으로 작성된다. 몇개의 행이 영향을 받아도, 실행된 구문의 수 만큼 트리거가 작동된다.
(1) 트리거의 사용용도
1) 관련테이블 변경 : 데이터 수정 전후의 테이블 상태 간 차이점을 찾아내고, 이 차이점에 따라 작업을 수행한다.
2) 참조 무결성 : 참조 무결성을 위해 FOREIGN KEY제약 조건을 사용하는 것이 좋지만, 그렇지 않을 경우 트리거를 활용한다. 무결성을 위반할 경우 변경을 허용하지 않거나 롤백을 하도록 처리한다.
3) CHECK제약조건
4) 사용자의 명령을 다른 작업으로 대체
(2) 트리거 발동
- 트리거는 사용자가 직접 호출할 수 없다. 트리거의 생성 권한도, 해당 테이블에 데이터 수정 권한이 있는 사용자가 만들은 트리거만 동작할 수 있다. 트리거의 동작을 위한 매개변수는 없고, 생성된 논리적 테이블을 기준으로 처리 작업을 수행하게 프로그래밍 한다. 해당 테이블들은 아래와 같음
|
데이터 수정작업 |
생성되는 논리적 테이블 |
|
INSERT |
inserted |
|
DELETE |
deleted |
|
UPDATE |
deleted inserted |
* TRUNCATE TABLE은 로그에 기록되지 않아(할당취소만 로그에 남는다) DELETE트리거가 동작하지 않는다.
(3) inserted및 deleted테이블 사용
1) inserted : 추가된 행에 대한 정보를 가지고 있다.
2) deleted : 삭제된 행에 대한 정보를 가지고 있다.
- UPDATE의 경우 delete후 insert이므로, deleted에 삭제된 행(기존값), inserted에 추가한 행(변경된값) 정보가 있다.
2. 트리거의 생성, 수정 및 삭제
(1) 트리거 생성구문
|
CREATE TRIGGER <트리거명> ON { table | view} [WITH ENCRYPTION] { { { FOR | AFTER | INSTEAD OF} { [INSERT][,][UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS [ { IF UPDATE ( <컬럼명> ) [ {AND | OR } UPDATE( <컬럼명> ) ] [ ...n] | IF(COLUMNS_UPDATED() { <비트연산자>} updated_bitmask) {<비교연산자>}column_bitmask[..n] }] <sql구문>[... n] } } |
1) FOR/AFTER : AFTER의 경우 7.0의 FOR키워드와 같은 의미로 사용된다. 트리거 작업이 실행되고 모든 제약조건이 처리된 후에 동작하는 트리거.
- AFTER트리거 안에서의 작업 순서는 무작위. 순서를 정하기 위해서 sp_settriggerorder프로시저를 사용하여 제일 처음/마지막에 수행될 트리거를 지정하거나 하나의 트리거 안에서 작업 순서에 맞게 코딩할것
2) INSTEAD OF : 다른 제약 조건이 처리되기 전에 가장 먼저 실행됨. 각 작업당 하나의 INSTEAD OF 트리거를 가질 수 있다. WITH CHECK OPTION으로 만들어진 뷰에는 적용하지 못한다. 해당 트리거가 반복하게 구현이 되어 있어도 반복 작업은 수행되지 않는다. INSTEAD OF INSERT에서 같은 테이블에 INSERT를 수행해도, 해당 구문은 트리거에 의해 다시 수행되지는 않는다.
3) WITH ENCRYPTION : 트리거 생성시 TEXT컬럼이 암호화 되어 들어간다
4) WITH APPEND : 6.5이하 버전의 하위 호환을 위해 존재.
5) NOT FOR REPLICATION : 복제를 통해 수정되는 데이터에 대한 트리거를 동작하지 않도록 할 때 사용
6) UPDATE : 지정한 컬럼에 대해 INSERT나 UPDATE작업을 확인. DELETE와는 함께 사용 못함. 지정한 컬럼에 변화가 있는경우 TRUE를 반환
7) COLUMNS_UPDATED() : 지정한 열이 삽입 또는 업데이트 되었는지 여부를 INSERT,UPDATE에서만 확인. 지정한 열이 변경되었는지 확인할 수 있다. 그러나 각 컬럼에 대해 bitmask연산을 한다. (변동이 있으면 1, 아니면 0)
- 예를들어, 컬럼 8개중 1,3,8이 업데이트 되었다면 결과는 다음과 같다. 1000 0101. 한번에 변경된 컬럼과 변경되지 않은 컬럼을 조회할 수 있다. 하지만 어느 컬럼이 업데이트 되었는지는 bitmask를 씌워 확인해야 한다.
(2) 트리거 수정과 삭제 : ALTER TRIGGER / DROP TRIGGER를 이용하여 제거
- 트리거가 적용된 테이블이 삭제되면 해당 트리거도 함께 제거된다.
3. 트리거 디자인
(1) AFTER와 INSTEAD OF비교
|
기능 |
AFTER트리거 |
INSTEAD OF 트리거 |
|
적용 |
테이블 |
테이블 및 뷰 |
|
테이블 또는 뷰당 개수 |
각 트리거 작업에 여러개 가능 |
각 트리거 작업당 한개 |
|
연계 참조 |
제한 없음 |
연계참조무결성제약조건의 대상이 되는 테이블에는 허용되지 않음 |
|
실행 |
제약조건처리,선언적 참조작업, inserted및 deleted테이블 작성, 트리거 작업 이후에 실행됨 |
제약조건 처리 이전. 트리거작업 대신 실행되고, 이후에 inserted및 deleted테이블 작성 |
|
실행순서 |
첫 번째 및 마지막 실행을 지정가능 |
적용 불가능 |
|
inserted, deleted테이블에서 text,ntext,image참조 |
사용 불가능 |
허용됨 |
(2) sp_settriggerorder
- 해당 프로시저는 AFTER트리거의 처음 또는 마지막에 작업될 트리거를 지정할 수 있다.
|
sp_settriggerorder [@triggername = ] '<트리거이름>', [@order=] '<값>, [@stmttype=] '<구문타입>' |
- @order에 들어갈 값은 다음 3가지중 하나이다.
1) First : AFTER트리거 중 가장 먼저 실행됨
2) Last : AFTER트리거 중 가장 나중에 실행됨
3) None : AFTER트리거 중 임의의 순서대로 처리됨
- 구문타입은 해당 트리거가 동작하는 구문 (INSERT,UPDATE,DELETE)을 정의.
(3) 트리거 작업 순서
1) INSERT, UPDATE, DELETE명령 수행
2) 트랜잭션 시작
3) INSTEAD OF 트리거가 존재하면 inserted, deleted테이블을 채운 후, INSTEAD OF트리거 수행.
- 테이블에 대한 작업을 수행하지 않으면 커밋 후 종료한다.
4) INSTEAD OF트리거가 존재하지 않거나, '3)'에서 테이블에 대한 작업이 필요하면, CHECK제약조건 검사
5) 오류가 있으면 롤백 후 작업 종료한다. 아닐경우 로그를 기록하고 inserted, deleted테이블을 채운 후, AFTER(FOR)트리거들을 수행한 후, 트랜잭션을 커밋
-출처 : SQL Server advanced programming
댓글