您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 DDL 觸發器實戰：防護資料庫架構變更

編輯：關於SqlServer

前言

SQL Server 2000 僅支援 DML 觸發器（INSERT/UPDATE/DELETE）。SQL Server 2005 引入 DDL 觸發器，可在資料庫層級或伺服器層級攔截 CREATE/ALTER/DROP 等結構定義語言事件，實現架構變更稽核、命名規範強制、防誤刪等治理需求。

DDL 觸發器核心語法

CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS
    sql_statement [ ...n ]

範圍： DATABASE（目前資料庫）或 ALL SERVER（整個執行個體）
時機： 僅 AFTER（無 INSTEAD OF）
事件： 單一事件（如 CREATE_TABLE）或事件群組（如 DDL_DATABASE_LEVEL_EVENTS）
事件資料： EVENTDATA() 回傳 XML，含事件時間、類型、SPID、登入帳號、物件名稱、完整 T-SQL 命令等

實戰範例 1：禁止資料庫層級的表與預存程序 DDL 操作

建立測試環境

CREATE DATABASE DDLTRTEST
GO
USE DDLTRTEST
GO
CREATE TABLE MYTABLE (ID INT, NAME VARCHAR(100))
INSERT INTO MYTABLE VALUES (1,'A'),(2,'B'),(3,'C')
GO
CREATE PROC USP_QUERYMYTABLE AS SELECT * FROM MYTABLE
GO

建立防護觸發器

CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    -- 取得觸發的 T-SQL 命令
    DECLARE @cmd NVARCHAR(MAX) = EVENTDATA().value(
        '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
    
    PRINT '您不被允許建立/修改/刪除任何資料表與預存程序'
    PRINT '嘗試命令：' + @cmd
    ROLLBACK
END
GO

測試效果

-- 嘗試修改表
ALTER TABLE MYTABLE ADD X INT
-- 結果：
-- (1 row(s) affected)
-- 您不被允許建立/修改/刪除任何資料表與預存程序
-- 訊息 3609，層級 16，狀態 2，行 1
-- 交易在觸發器中結束。批次已中止。

-- 嘗試刪除表
DROP TABLE MYTABLE
-- 同樣被攔截回滾

啟用/停用觸發器

-- 停用
DISABLE TRIGGER STOP_DDL_on_Table_and_PROC ON DATABASE

-- 啟用
ENABLE TRIGGER STOP_DDL_on_Table_and_PROC ON DATABASE

實戰範例 2：伺服器層級防護資料庫建立/修改/刪除

CREATE TRIGGER STOP_DDL_on_Database
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
    PRINT '您不被允許建立/修改/刪除任何資料庫'
    ROLLBACK
END
GO

實戰範例 3：強制預存程序命名慣例（進階 EVENTDATA 解析）

CREATE TRIGGER ddlTrigger_ProcNamingConvention
ON DATABASE
AFTER CREATE_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    DECLARE @eventData XML = EVENTDATA()
    DECLARE @objName NVARCHAR(200) = @eventData.value(
        '(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)')
    
    IF LEFT(@objName, 2) <> 'pr'
    BEGIN
        PRINT '預存程序必須以 "pr" 開頭：' + @objName
        ROLLBACK TRANSACTION
    END
    ELSE
        PRINT '命名符合規範：' + @objName
END
GO

EVENTDATA() 回傳 XML 結構範例


  CREATE_PROCEDURE
  2005-10-20T00:52:16.160
  51
  MYDBSERVER
  DOMAIN\jpapa
  dbo
  AdventureWorks
  dbo
  test1
  PROCEDURE
  
    
    create proc test1 as select getdate()

進階應用：稽核記錄至資料表

CREATE TABLE DDL_AuditLog (
    AuditID BIGINT IDENTITY PRIMARY KEY,
    EventData XML NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
)

CREATE TRIGGER trg_DDL_Audit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    INSERT INTO DDL_AuditLog (EventData) VALUES (EVENTDATA())
END

關鍵限制與最佳實務

限制/特性	說明
同步執行	DDL 觸發器同步執行，避免耗時操作（寄信、跨伺服器查詢），否則影響 DDL 回應時間
無 inserted/deleted 表	不同於 DML 觸發器，僅能透過 EVENTDATA() 取得資訊
可回滾交易	ROLLBACK 可阻止 DDL 操作生效
巢狀觸發	觸發器內執行 DDL 會再次觸發，需謹慎設計避免無限迴圈
效能建議	僅執行必要邏輯，快速完成；非同步需求改用 Service Broker + Event Notifications