SQL Server 2005 DDL 觸發器防止誤刪資料表

建立測試環境

CREATE DATABASE demo; GO
USE demo; GO
CREATE TABLE T_User (UserName VARCHAR(64)); GO

建立資料庫級 DDL 觸發器

CREATE TRIGGER prevent_drop_table
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    RAISERROR(N'不允許刪除資料表。', 10, 1);
    PRINT N'DROP TABLE 嘗試發生於資料庫 ' + DB_NAME() + N'。';
    PRINT CONVERT(NVARCHAR(1000), EVENTDATA());
    ROLLBACK;  -- 核心：回滾觸發動作
END
GO

測試效果

USE demo;
DROP TABLE T_User;
-- 結果：
-- 不允許刪除資料表。
-- DROP TABLE 嘗試發生於資料庫 demo。
-- ...DROP_TABLE...
-- 訊息 3609，層級 16，狀態 2：交易已結束，觸發器中發生錯誤。

關鍵點解析

• ON DATABASE：資料庫範圍，若要伺服器級用 ON ALL SERVER (SQL 2008+)。
• FOR DROP_TABLE：攔截 DROP TABLE，亦可用 FOR DDL_TABLE_EVENTS 涵蓋 CREATE/ALTER/DROP。
• EVENTDATA()：回傳 XML，含事件類型、物件名稱、登入帳號、TSQL 指令等，可寫入稽核表。
• ROLLBACK：必須在觸發器內回滾，否則 DDL 仍會生效。

注意事項

• 不影響暫存表 (#temp, ##global) 的建立/刪除。
• 系統管理員 (sysadmin) 可停用觸發器：DISABLE TRIGGER prevent_drop_table ON DATABASE;，故屬「防誤刪」而非「絕對禁止」。
• 生產環境建議搭配 DDL_EVENTS 記錄完整稽核軌跡。

延伸：記錄稽核資訊

CREATE TABLE DDL_AuditLog (
    EventDate   DATETIME DEFAULT GETDATE(),
    LoginName   SYSNAME,
    EventType   NVARCHAR(100),
    ObjectName  NVARCHAR(256),
    TSQLCommand NVARCHAR(MAX)
);

-- 在觸發器中
INSERT INTO DDL_AuditLog (LoginName, EventType, ObjectName, TSQLCommand)
VALUES (
    SYSTEM_USER,
    EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),
    EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
);

部署此觸發器，能有效防止開發/維護人員手滑刪除正式表，並留下完整稽核軌跡。