SqlServer DDL 触发器

DDL 触发器主要作用:

要防止对数据库架构进行某些更改。

希望数据库中发生某种情况以响应数据库架构中的更改。

要记录数据库架构中的更改或事件。

仅在运行触发 DDL 触发器的 DDL 语句后，DDL触发器才会激发。DDL触发器无法作为INSTEADOF 触发器使用。

测试1：

-- 数据库级别的触发器(只对当前数据库有用)
CREATE TRIGGER TR_DDL_Table
ON DATABASE 
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
--或者DDL_TABLE_EVENTS
AS 
BEGIN
   PRINT '触发器TR_DDL_TableSafety 已禁止对表进行DDL操作!' 
   ROLLBACK
END

--激活和禁用触发器
ENABLE TRIGGER TR_DDL_Table ON DATABASE;
DISABLE TRIGGER TR_DDL_Table ON DATABASE;

--删除触发器
DROP TRIGGER TR_DDL_Table ON DATABASE 

-- drop table test
create table test(id int)

触发器TR_DDL_TableSafety 已禁止对表进行DDL操作!

消息3609，级别16，状态2，第1行

事务在触发器中结束。批处理已中止。

测试2：

--  服务器级别的触发器
CREATE TRIGGER TR_DDL_Database 
ON ALL SERVER 
FOR DDL_SERVER_LEVEL_EVENTS 
AS 
    DECLARE @EVENTDATA XML;
    SET @EVENTDATA = EVENTDATA();
    PRINT '触发器TR_DDL_Database 已禁止对数据库进行DDL操作!'
    SELECT @EVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType
        , @EVENTDATA.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime
        , @EVENTDATA.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)') AS ServerName
        , @EVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName
        , @EVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText
    ROLLBACK
GO

--  创建数据库
--  drop database test
CREATE DATABASE [test] 
ON  PRIMARY ( 
NAME = N'test', 
FILENAME = N'D:\test.mdf' , 
SIZE = 3072KB , 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB 
)
--进行了回滚操作并输出信息

测试3：

--  不回滚,但记录操作信息
--  DROP TABLE TABLE_SERVER_LEVEL_EVENTS
CREATE TABLE MASTER.DBO.TABLE_SERVER_LEVEL_EVENTS
(
EventType       NVARCHAR(100),  
PostTime        DATETIME,
ServerName      NVARCHAR(100),
DatabaseName    NVARCHAR(100),
CommandText     NVARCHAR(MAX),
)

--  服务器级别的触发器
CREATE TRIGGER TR_DDL_Database 
ON ALL SERVER 
FOR DDL_SERVER_LEVEL_EVENTS 
AS 
    DECLARE @EVENTDATA XML;
    SET @EVENTDATA = EVENTDATA();
    INSERT INTO MASTER.DBO.TABLE_SERVER_LEVEL_EVENTS(EventType,PostTime,ServerName,DatabaseName,CommandText)
    SELECT @EVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType
        , @EVENTDATA.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime
        , @EVENTDATA.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)') AS ServerName
        , @EVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName
        , @EVENTDATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText
GO


CREATE DATABASE [test] 
ON  PRIMARY ( 
NAME = N'test', 
FILENAME = N'D:\test.mdf' , 
SIZE = 3072KB , 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB 
)

DROP DATABASE [test]

SELECT * FROM MASTER.DBO.TABLE_SERVER_LEVEL_EVENTS

--删除触发器
DROP TRIGGER TR_DDL_Database ON ALL SERVER
GO

--查看 数据库级别 的触发器及事件
select a.name,a.parent_class_desc,b.type_desc
from sys.triggers a inner join sys.trigger_events b
on a.object_id=b.object_id

--查看 服务器级别 的触发器及事件
select a.name,a.parent_class_desc,b.type_desc
from sys.server_triggers a inner join sys.server_trigger_events b
on a.object_id=b.object_id

更多参考：

​​DDL 触发器​​

​​DDL 事件​​

​​DDL 事件组​​