如何在SQL Server里编写DDL触发器以防止误删数据库表？

DDL触发器必须显式启用才生效，新建的CREATE TRIGGER ON DATABASE默认禁用；须用EVENTDATA()解析XML提取对象名，配合TRYCATCH、RAISERROR≥16级及ROLLBACK才能硬拦截DROP操作；TRUNCATE TABLE无法被DDL触发器捕获，需权限控制兜底。

DDL触发器必须显式启用才生效

新建的 CREATE TRIGGER ... ON DATABASE FOR DROP_TABLE 默认是禁用状态，哪怕语法完全正确，它也不会响应任何 DROP TABLE。很多线上事故就源于“建完就以为万事大吉”。

检查是否启用：SELECT is_disabled FROM sys.triggers WHERE name = 'tr_block_drop' —— 返回 1 表示禁用。

启用命令必须带作用域：ENABLE TRIGGER tr_block_drop ON DATABASE（当前库）或 ENABLE TRIGGER tr_block_drop ON ALL SERVER（全局，慎用）。

注意：服务器级触发器（ON ALL SERVER）对用户数据库里的 DROP TABLE 无效，它只响应 CREATE LOGIN、ALTER DATABASE 这类跨库事件。

必须用 EVENTDATA() 提取对象名，不能用 OBJECT_NAME()

OBJECT_NAME() 在 DDL 触发器中不可靠——表已被删或尚未创建，函数直接返回 NULL。真正可用的是 EVENTDATA() 返回的 XML。

正确提取表名：CAST(EVENTDATA() AS XML).value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

提取 Schema：CAST(EVENTDATA() AS XML).value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')

必须用 TRY...CATCH 包裹解析逻辑，否则 XML 结构微调（如新版 SQL Server 改字段名）会导致触发器报错中断事务，反而阻断合法运维。

常见错误：XPath 写成 /ObjectNamee 或漏掉 [1]，整个判断就跳过，防护形同虚设。

RAISERROR 必须 ≥16 级且紧跟 ROLLBACK

级别低于 16 的错误（比如 RAISERROR('...', 10, 1)）只是警告，事务继续执行，DROP TABLE 仍会成功。

必须用 RAISERROR('禁止删除表', 16, 1) 或更高（16–25），才能让 SQL Server 中断当前批处理并回滚。

紧随 RAISERROR 后必须加 ROLLBACK，不能依赖事务自动终止；否则批处理可能继续执行后续语句，造成意外后果。

别在触发器里写 PRINT 或 SELECT —— 这些不中断事务，DROP 照常完成。

白名单不能只比对登录名，要绑定会话上下文

硬编码 ORIGINAL_LOGIN() IN ('sa', 'dba_admin') 是常见漏洞：所有归档脚本都用同一个账号运行，那这个账号删任何表都畅通无阻。

应结合 PROGRAM_NAME()、HOST_NAME() 或自定义上下文（SET CONTEXT_INFO）做联合判断。

例如应用层执行清理前先设标识：SET CONTEXT_INFO 0x5472756E636174654A6F62，触发器中用 CONTEXT_INFO() 提取校验。

临时表放行不能靠 LIKE '%temp%' —— 攻击者可建 user_temp_drop_me 绕过；应严格匹配系统命名规范（如以 # 或 ## 开头）。

DDL 触发器拦不住 TRUNCATE TABLE，它根本不走 DDL 流程，也不触发任何触发器。这点最容易被忽略，必须靠权限管控（如 REVOKE ALTER ON SCHEMA::dbo FROM [app_user]）或审计日志兜底。