SqlServer DDL 触发器

DDL 触发器主要作用:

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

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

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

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

测试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 CommandText
GO


CREATE DATABASE [test] 
ON  PRIMARY ( 
NAME = N'test',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,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 事件组

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!