[SQL] DDL Trigger

DDL Trigger 可以用來追蹤與監控資料庫結構的變化，EX：紀錄誰何時對 Table schema 進行變化、新增登入者等，DDL 事件會觸發 DDL Trigger，DDL 事件則是指 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 關鍵字開頭的 T-SQL 語法。

語法

CREATE TRIGGER trigger_name     -- DDL Trigger 名稱 
ON  { ALL SERVER | DATABASE }   -- 指定伺服器或資料庫層級
    [ WITH 
          ENCRYPTION ,          -- 是否加密
          EXECUTE AS User       -- 使用某 User 的權限來執行 DDL Trigger
    ]
FOR { event_type | event_group }-- 指定觸發的 DDL 事件或 DDL 群組事件
AS
    BEGIN

        -- 執行的 T-SQL 語法

    END

EVENTDATA()函數

DDL Trigger 執行時，使用 EVENTDATA() 擷取 DDL 相關資訊，回傳值為 XML，可以利用 XQuery 來解析其內容，內容可以儲存在 Table 中供後續分析追蹤或利用 DataBase Mail 發信通知相關人員。

• LoginName：登入 SQL Server 來執行 DDL 語法的帳號
• ObjectName：對哪個帳號進行加入、修改或刪除動，以上述圖型為例，是要新增 DDLTest 帳號

透過對 CREATE_TABLE 事件建立 DDL 觸發程序，把建立 Table 的使用者紀錄至 Windows 事件檢視器

-- Step 1：建立 ddlCreateTable
CREATE TRIGGER ddlCreateTable
ON DATABASE
FOR CREATE_TABLE -- 建立 Table 事件
AS
  DECLARE @text NVARCHAR(100)
  SET @text = suser_sname() + N' 新建立一個資料表'
  RAISERROR(@text,10,1) WITH LOG -- 重點

-- Step 2：建立一個 Table 觸發 ddlCreateTable 並至 Windows 事件檢視器查看紀錄
CREATE TABLE [dbo].[CreateTableTest]
(
  [id] [int] NULL
)

禁止任何使用者修改或刪除 Table

Trigger 預設是與觸發該 Trigger 的語法包在一個交易內一起執行，所以可以透過 ROLLBACK 回復 DDL 語法造成的影響。

-- Step 1：建立 ddlModifyTable
CREATE TRIGGER ddlModifyTable
ON DATABASE
FOR ALTER_TABLE , DROP_TABLE -- 修改或刪除 Table 事件
AS
    PRINT N'禁止修改或刪除 Table'
    ROLLBACK -- 重點

-- Step2：嘗試刪除 Table 就會被禁止
DROP TABLE [dbo].[CreateTableTest]

利用事件群組，禁止新增、修改或刪除 Login 帳號並擷取 EVENTDATA() 內資訊來發 E-Mail 通知

-- Step 1：建立 ddlLogin DDL
CREATE TRIGGER [ddlLogin]
   ON ALL SERVER
   FOR DDL_LOGIN_EVENTS
AS
  BEGIN
  
      SET NOCOUNT ON
 
      DECLARE
          @data xml,
          @EventType varchar(100),
          @EventTime datetime,
          @ServerName varchar(100),
          @AffectedLoginName varchar(100),
          @WhoDidIt varchar(100),
          @TSQL nvarchar(2000),
          @Text varchar(800)
 
      SET @data = EVENTDATA()
      SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
      SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
      SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
      SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)') 
          -- 表示要把哪個帳號，加入、修改或刪除
      SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)') 
          -- 執行此動作帳號
      SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(2000)')
 
      SET @Text = 
          'DDL_Login_Event: ' + @EventType + char(10) +
          'Event Occured at: ' + CONVERT(varchar(30) , @EventTime) + char(10) +
          'ServerName: ' + @ServerName + char(10) +
          'Affected Login Name: ' + @AffectedLoginName + char(10) +
          'Event Done by: ' + @WhoDidIt
 
      ROLLBACK -- 把新增、修改、刪除登入帳戶動作給 ROLLBACK
 
      EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'DataBase Mail Profile' ,
           @recipients = 'DBA@gmail.com' ,
           @subject = 'SQL Server notification - SQL Server Login Event is triggered' ,
           @body = @Text ,
           @body_format = 'TEXT'
   
  END

-- Step 2：嘗試刪除已建立的 Login 帳號
DROP LOGIN [ServerName\DDLTest]

禁止使用 sp_configure T-SQL 語法，更改伺服器參數

20130827 新增此範例，ALTER_INSTANCE 事件是從 SQL Server 2008 開始提供

-- Step 1：建立 disableSP_Configure
CREATE Trigger disableSP_Configure
ON ALL SERVER
FOR ALTER_INSTANCE
AS
    PRINT N'無法利用 sp_configure 更改伺服器參數'
    ROLLBACK
GO

-- Step 2：利用 sp_configure 更改伺服器選項來觸發
EXEC sp_configure 'xp_cmdshell' , 1
RECONFIGURE
GO

查詢 DDL Trigger

-- 查詢 Database DDL Trigger
SELECT
    T.Name AS DDLName ,
    M.definition ,
    STUFF
      (
        (
          SELECT ',' + type_desc 
          FROM sys.trigger_events AS TE
          WHERE T.[object_id] = TE.[object_id]
          FOR XML PATH('')
        )
        ,1,1,''
      ) AS type_desc
FROM sys.triggers AS T
  JOIN sys.sql_modules AS M ON M.[object_id] = T.[object_id]
WHERE T.parent_class = 0 -- 0：DDL Trigger、1：DML Trigger
  AND T.Name = 'TriggerName'

-- 查詢 Server DDL Trigger
SELECT
    T.Name AS DDLName ,
    M.definition ,
    STUFF
      (
        (
          SELECT ',' + type_desc 
          FROM sys.server_trigger_events AS TE
          WHERE T.[object_id] = TE.[object_id]
          FOR XML PATH('')
        )
        ,1,1,''
      ) AS type_desc
FROM sys.server_triggers AS T
  JOIN sys.server_sql_modules AS M ON M.[object_id] = T.[object_id]
WHERE T.Name = 'TriggerName'

刪除 DDL Trigger

刪除 DDL TRIGGER 要搭配 ON DATABASE 或 ON ALL SERVER 選項，否則 SQL Server 會以為要刪除的是 DML TRIGGER，會傳回找不到物件的錯誤訊息。

DROP TRIGGER TriggeName ON DATABASE
DROP TRIGGER TriggerName ON ALL SERVER

MSDN 參考資料
• DDL 觸發程序
• DDL 事件、與 DDL 觸發程序搭配使用的事件群組 => 一目了然的事件分類表
• 使用 EVENTDATA 函數、EVENTDATA
• Introduction to XQuery in SQL Server 2005
• sys.triggers、sys.sql_modules、sys.trigger_events
• sys.server_triggers、sys.server_trigger_events、sys.server_sql_modules、sys.server_trigger_events
• IS_SRVROLEMEMBER

參考資料
• DDL Triggers and LOGON Triggers
• Understanding DDL Triggers in SQL Server 2005
• Using DDL Triggers in SQL Server 2005 to Capture Schema Changes
• Stairway to XML: Level 4 - Querying XML Data
• SQL Server: Three Common DDL Change Log Methods