DDL 트리거

DDL 트리거의 자세한 내용은 MSDN DDL 트리거를 참조 바랍니다.

SQL Server에는 크게 3가지 트리거가 있습니다.
DML 트리거
LOGON 트리거
DDL 트리거

DML 트리거는 트리거에 정의된 테이블이나 뷰에 영향을 주는 DML(데이터 조작 언어) 이벤트에 대한 응답으로 실행됩니다.
LOGON 트리거는 LOGON 이벤트에 대한 응답으로 실행됩니다.
DDL 트리거는 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 실행됩니다.

DDL 트리거는 다음과 같은 경우에 사용하게 됩니다.
1.데이터베이스 스키마에 대한 특정 변경 작업을 방지하려는 경우
2.데이터 스키마가 변경될 때 데이터베이스에서 특정 작업이 수행되게 하려는 경우
3.데이터베이스 스키마의 변경 내용이나 이벤트를 기록하려는 경우

DML 트리거가 inserted, deleted라는 두 개의 특수 테이블을 사용하여 정의된다면
DDL 트리거는 EVENTDATA()라는 함수가 반환하는 XML형식의 데이터를 사용하여 정의됩니다.

간단하게 C1이라는 컬럼 하나를 가진 T1이라는 테이블을 만들때 EVENTDATA()의 내용을 살펴보겠습니다.

    CREATE_TABLE
    2014-04-09T15:41:34.880
    53
    MyComputer\SQL2008_INST1
    DBUser
    dbo
    UserDB
    dbo
    T1
    TABLE
    
        
        CREATE TABLE T1 (C1 INT)
    

이 데이터에 value()메서드를 사용하여 원하는 데이터를 뽑을 수 있습니다.

좀 복잡한 DDL 트리거를 만들어보겠습니다.
DDLHistory라는 테이블 DDL 이벤트를 로깅하고
DBUser라는 로그인이 테이블을 생성, 수정, 삭제하는 것을 막는 DDL 트리거입니다.
각 DB마다 DDL 트리거를 만들수도 있지만
저는 서버수준에서 DDL트리거를 만들어서
새로 DB가 생성되는 경우에도 DDL History가 남겨지도록 만들어봤습니다.
유저가 특정 테이블에 INSERT권한이 없는 경우를 대비하여 WITH EXECUTE AS 'sa'을 사용합니다.
CREATE TRIGGER TRG_DDL ON ALL SERVER WITH EXECUTE AS 'sa' FOR DDL_EVENTS

AS

SET NOCOUNT ON

DECLARE @DATA XML = EVENTDATA()
DECLARE @EventType VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)')
DECLARE @PostTime DATETIME = @DATA.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
DECLARE @LoginName VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)')
DECLARE @UserName VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/UserName)[1]', 'VARCHAR(128)')
DECLARE @HostName NVARCHAR(128) = HOST_NAME()
DECLARE @DatabaseName VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)')
DECLARE @SchemaName VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(128)')
DECLARE @ObjectName VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(128)')
DECLARE @ObjectType VARCHAR(128) = @DATA.value('(/EVENT_INSTANCE/ObjectType)[1]', 'VARCHAR(128)')
DECLARE @CommandText NVARCHAR(MAX) = @DATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)')

IF @DatabaseName = '' SET @DatabaseName = 'master'

DECLARE @SCRIPT NVARCHAR(MAX) = 'USE ' + @DatabaseName + '
SET NOCOUNT ON
IF NOT EXISTS (SELECT 1 FROM ' + @DatabaseName + '.SYS.OBJECTS WHERE TYPE = ''U'' AND NAME = ''DDLHistory'') BEGIN
        CREATE TABLE ' + @DatabaseName + '.dbo.DDLHistory
        (ID INT NOT NULL IDENTITY(1,1)
        , EventType VARCHAR(128)
        , PostTime DATETIME
        , LoginName VARCHAR(128)
        , UserName VARCHAR(128)
        , HostName NVARCHAR(128)
        , DatabaseName VARCHAR(128)
        , SchemaName VARCHAR(128)
        , ObjectName VARCHAR(128)
        , ObjectType VARCHAR(128)
        , CommandText NVARCHAR(MAX))

        ALTER TABLE ' + @DatabaseName + '.dbo.DDLHistory ADD CONSTRAINT XPK_DDLHistory PRIMARY KEY NONCLUSTERED (ID)
        CREATE CLUSTERED INDEX IDX_DDLHistory_ObjectName_PostTime ON ' + @DatabaseName + '.dbo.DDLHistory (ObjectName, PostTime DESC)
        CREATE NONCLUSTERED INDEX IDX_DDLHistory_PostTime_ObjectName ON ' + @DatabaseName + '.dbo.DDLHistory (PostTime DESC, ObjectName)
        CREATE NONCLUSTERED INDEX IDX_DDLHistory_EventType_UserName ON ' + @DatabaseName + '.dbo.DDLHistory (EventType, UserName)
END

INSERT INTO ' + @DatabaseName + '.dbo.DDLHistory (EventType, PostTime, LoginName, UserName, HostName, DatabaseName, SchemaName, ObjectName, ObjectType, CommandText)
VALUES (@EventType, @PostTime, @LoginName, @UserName, @HostName, @DatabaseName, @SchemaName, @ObjectName, @ObjectType, @CommandText)'

EXEC SP_EXECUTESQL @SCRIPT,
N'@EventType VARCHAR(128)
, @PostTime DATETIME
, @LoginName VARCHAR(128)
, @UserName VARCHAR(128)
, @HostName NVARCHAR(128)
, @DatabaseName VARCHAR(128)
, @SchemaName VARCHAR(128)
, @ObjectName VARCHAR(128)
, @ObjectType VARCHAR(128)
, @CommandText NVARCHAR(MAX)',
@EventType = @EventType
, @PostTime = @PostTime
, @LoginName = @LoginName
, @UserName = @UserName
, @HostName = @HostName
, @DatabaseName = @DatabaseName
, @SchemaName = @SchemaName
, @ObjectName = @ObjectName
, @ObjectType = @ObjectType
, @CommandText = @CommandText

IF @LoginName = 'DBUser' AND @EventType IN ('CREATE_TABLE', 'ALTER_TABLE', 'DROP_TABLE') BEGIN
    PRINT '그라믄 안데! 테이블 만들고 그라믄 안데!!!'
    ROLLBACK
END ELSE BEGIN
    PRINT '명령이 완료되었습니다.'
END
이 트리거를 만든 후 DBUser로 로긴하여 저장 프로시저 하나와 테이블 하나를 만들어 보았습니다.

테이블을 만들려는 시도는 실패하게 됩니다.
DDLHistory 테이블을 조회하면 DDL문의 실행기록을 볼 수 있습니다.
만약 프로시저를 잘못 수정했다면 DDLHistory 테이블에서 과거 기록을 찾아서 SP를 복구 할 수 있습니다.
처음부터 DDL 트리거를 만든것이 아니라면 sys.sql_modules 카다로그 뷰에서 모든 소스를 찾아서 DDLHistory 테이블에 적재해두면 좋습니다.


PS.
서버에 정의된 트리거의 목록을 보려면 sys.server_triggers 카다로그 뷰를 사용합니다.
서버에 정의된 트리거가 실행되는 이벤트를 확인하려면 sys.server_trigger_events 카다로그 뷰를 사용합니다.
서버에 정의된 트리거의 소스를 보려면 sys.server_sql_modules 카다로그 뷰를 사용합니다.