MSSQL DDL Trigger 사용법 및 특징

DDL Trigger는 SQL SERVER의 스키마 변경에 대한 이력을 관리하거나 변경하지 못하게 하는 경우 주로 사용합니다.
예를 들면 테이블 변경 이력이 필요한 경우 DDL Trigger 사용합니다.

 

DDL Trigger 특징.

• 다양한 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 DDL Trigger를 사용합니다.
• CREATE, ALTER, DROP, GRANT, DENY, REVOKE 시작하는 Transact-SQL 문에서 DDL Trigger를 사용할 수 있습니다.
• DDL Trigger는 특정 사용자가 데이터베이스 스키마에 대한 변경을 시도할 때 변경을 방지할 수 있습니다.
• DDL Trigger는 데이터베이스 스키마에 대한 변경 이력을 관리할 때 매우 유용하게 사용합니다.

DDL Trigger 문법.

Syntax.

CREATE TRIGGER trigger_name
ON { DATABASE |  ALL SERVER}
[WITH ddl_trigger_option]
FOR { event_type | event_group }
AS 
    { T-sql 문장}

문법 설명입니다.

• trigger_name: 생성되는 Trigger 이름입니다.
• ON 절 옵션은 데이터베이스는 데이터베이스 또는 모든 서버 범위 이벤트에 대해 Trigger를 실행하도록 지정합니다.
• ddl_trigger_option은 Encryption 또는 EXECUTE AS 절을 지정할 수 있습니다. 
• Encryption은 Trigger를 암호화할 수 있습니다.
• EXECUTE AS는 Trigger가 실행되는 유저를 정의합니다.
• event_type은 Trigger가 CREATE_TABLE, ALTER_TABLE 등을 설정하는 이벤트입니다.
• event_group은 event_type의 그룹입니다.

DDL Trigger 사용법.

DDL Trigger 생성.

TABLE 변경 못 하게 하는 Trigger입니다.

USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_DDL_Trigger;
-- Create Sample Table 
CREATE TABLE AurumGuide_DDL_Trigger(
   AurumId           varchar(100),
   loginTime         datetime 
);
go
-- TABLE 변경 금지.
CREATE TRIGGER TR_AurumGuide_DDL_Trigger   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "AurumGuide_DDL_Trigger" to drop or alter tables!'   
   ROLLBACK;  
go
-- DROP TABLE
drop table AurumGuide_DDL_Trigger;

 

TABLE 변경 이력을 테이블 XML 형태로 저장하는 예제입니다.

 

-- 테이블 변경 이력.
CREATE TABLE dbo.AurumGuide_DDL_Trigger_Log(
	LogID int IDENTITY(1,1) PRIMARY KEY,     
	ChangeId   varchar(100),
	DateChanged datetime,
	EventInfo   xml NULL
);
-- 트리거 생성
ALTER TRIGGER TR_AurumGuide_DDL_Trigger_Log
ON DATABASE
FOR	
	CREATE_TABLE,
	ALTER_TABLE, 
	DROP_TABLE
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO dbo.AurumGuide_DDL_Trigger_Log 
	(ChangeId, DateChanged, EventInfo)
	VALUES 
	(USER,GETDATE(),EVENTDATA());
END;
-- 테이블 변경
USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_DDL_Trigger;
-- Create Sample Table 
CREATE TABLE AurumGuide_DDL_Trigger(
   AurumId           varchar(100),
   loginTime         datetime 
);
go
-- 변경이력 확인 
SELECT *
 FROM AurumGuide_DDL_Trigger_Log

 

소스 코드 설명입니다.

TABLE 변경 이력을 테이블 XML 형태로 저장하는 설명 입니다.

DDL Trigger 수정.

• 저장 프로시저 같게 ALTER TRIGGER 이용해서 수정할 수 있습니다.

DDL Trigger 비활성화 및 삭제.

• 소스 코드입니다.

-- 비활성화 문법
DISABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]
--  비활성화 소스 코드
DISABLE TRIGGER TR_AurumGuide_DDL_Trigger ON DATABASE;
-- 삭제 
DROP TRIGGER TR_AurumGuide_DDL_Trigger ON DATABASE;