[MS SQL] DDL 트리거 (Trigger)

[MS SQL] DDL 트리거 (Trigger)

 

 

DDL 트리거는 DDL문에 의해서 작동되는 트리거로서 주로 CREATE, ALTER, DROP 등으로 작동됩니다. 테이블이 아닌 데이터베이스나 SQL Server에 대해서 작동되며 ALTER(또는 FOR)트리거만이 지원합니다(INSTEAD OF 트리거는 지원 불가), 또한 데이터베이스 작업의 감사, 통제 등 데이터베이스의 관리 작업을 수행합니다. DML 트리거와 달리 DDL 트리거는 테이블이나 뷰에서 UPDATE, INSERT, DELETE 구문에 응답하여 시작되지 않고 다양한 DML 이벤트에 응답하여 시작됩니다. DDL과 같은 작업을 수행하는 특정 시스템 저장 프로시저에서 DDL 트리거가 발생 가능합니다.

 

DDL 트리거를 사용하는 경우는 다음과 같습니다.

1) 데이터베이스 스키마에 대한 특정 변경 작업을 방치하려는 경우

2) 데이터 스키마가 변경될 때 데이터베이스에서 특정 작업이 수행되게 하려는 경우

3) 데이터베이스 스키마의 변경 내용이나 이벤트를 기록하려는 경우

 

 

■ DDL 트리거 생성

DDL 트리거 구문의 기본 형식

CREATE TRIGGER 트리거 이름
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type | event_group }
AS
	SQL 문장;

 

DDL 트리거에서 알아둘 사항은 이벤트 형식과 이벤트 그룹입니다.

 

이벤트 형식(event_type)

이벤트 형식은 크게 데이터베이스 범위의 DDL 이벤트와 서버 범위의 DDL 이벤트 두 가지 형식으로 나눌 수 있습니다. 데이터베이스 범위의 DDL 이벤트는 데이터베이스 내의 개체를 생성, 수정, 삭제할 경우에 발생합니다.

서버 범위의  DDL 이벤트는 SQL Server 서버에 적용되는 DDL 이벤트입니다.

이벤트는 하나의 T-SQL 구문에 해당합니다. 수정된 구문에서는 키워드 간에 밑줄('_')이 포함됩니다.

 

 

이벤트 그룹(event_group)

이벤트 그룹(event_group)은 몇몇 개의 관련 있는 이벤트 형식을 묶은 집합입니다. 트리 구조로 되어 있으며 서버 레벨 이벤트의 가장 최상위로는 'DDL_SERVER_LEVEL_EVENTS' 그룹입니다.

 

 

실습 : DDL 트리거 생성

1. 먼저 테이블을 새롭게 만듭니다.

create table TRGTest (
	no int identity primary key, -- 자동 증가
);

2. DDL 트리거를 정의합니다.

CREATE TRIGGER DDL_TRG ON DATABASE AFTER DROP_TABLE
AS
PRINT('테이블 삭제')
ROLLBACK TRANSACTION
GO

3. 테이블을 제거하고 나면 트리거가 발생하여 테이블을 제거하는 DDL 명령을 취소합니다. 그렇기 때문에 테이블을 SELECT 문으로 조회할 수 있습니다.

DROP TABLE TRGTest
GO
SELECT * FROM TRGTest
GO

 

 

 

 

 

■ DDL 트리거 수정

ALTER 구문을 사용하여 트리거를 수정할 수 있습니다. 다음은 DDL 트리거를 수정하는 기본 형식입니다.

ALTER TRIGGER [ 스키마 이름. ] 트리거 이름
ON [ ALL SERVER | DATABASE ]
[ WITH ENCRYPTION ]
{ FOR | AFTER }
{ event_type | event_group }
AS { SQL 구문 [ , ... n ] [ ; ] }

 

실습 : DDL 트리거 수정

1. DDL 트리거를 수정합니다.

ALTER TRIGGER DDL_trg
ON DATABASE
AFTER CREATE_TABLE
AS
PRINT('테이블 생성')
ROLLBACK TRANSACTION;
GO

2. 테이블을 생성하고 나면 트리거가 발생하여 테이블을 생성하는 DDL 명령을 취소합니다. 그러므로 테이블에 INSERT 문으로 데이터를 추가하려고 하면 테이블이 존재할 수 없다고 오류 메시지가 출력됩니다.

 

 

■ 트리거의 삭제

DROP TRIGGER 구문을 사용해서 트리거를 삭제할 수 있습니다.

DROP TRIGGER [ 스키마 이름. ] 트리거 이름
ON { ALL SERVER | DATABASE }

DDL 트리거를 삭제시에는 트리거가 속한 범위를 지정해주어야 삭제됩니다.

EX) DROP TRIGGER DDL_TRG ON DATABASE

 

 

 

 

 

출처 : (서적) 한번에 이해되는 MS SQL Server