SQL Server DDL触发器

peiyuan1030

　　DDL 触发器作用：
　　DDL 触发器主要用于防止对数据库架构、视图、表、存储过程等进行的某些修改。
　　DDL 触发器事件：
　　DDL 触发器在创建用来监视并响应该数据库或服务器实例中的活动的事件通知时，可以指定相应事件类型或事件组。
　　超链接：DDL 事件 和 DDL 事件组
　　DDL 触发器类别：
　　DDL 触发器分为：数据库（DataBase）级别和服务器（Server）级别
　　DDL 数据库（DataBase）级别触发器：
　　因为 DDL 触发器不在架构范围内，所以不会在 sys.objects 目录视图中出现，无法使用 OBJECT_ID 函数来查询数据库中是否存在 DDL 触发器。
　　可以使用相应的目录视图来查询架构范围以外的对象。
　　对于 DDL 数据库级别触发器，可使用 sys.triggers 视图。对于 DDL 服务器级别触发器，可使用 sys.server_triggers 视图。
　　创建一个修改和删除表（table）的 DDL 数据库级别触发器：
　　

　　
if exists(select * from sys.triggers where parent_class=0 and name='trigger_DDL_Table')
　　

　　
drop trigger trigger_DDL_Table on database        
　　
go
　　
create trigger trigger_DDL_Table   
　　
on database
　　
for drop_table,alter_table        
　　
as
　　print '触发器 trigger_DDL_Table 已禁止对表进行DDL的 drop、alter 操作'
　　rollback   
　　
go
　　

　　
drop table Student_back
　　

　　
alter table Student_back add cc int null default(1)
　　

　　创建一个在工作时间不允许创建、修改和删除视图（view）的 DDL 数据库级别触发器：
　　

　　
if exists(select * from sys.triggers where parent_class=0 and name='trigger_DDL_View')
　　

　　
drop trigger trigger_DDL_View on database        
　　
go
　　
create trigger trigger_DDL_View   
　　
on database
　　
for create_view,drop_view,alter_view        
　　
as
　　if(DATEPART(hour,getdate()) between 9 and 17)
　　begin
　　declare @EventData xml
　　set @EventData=EVENTDATA();
　　

　　select '触发器 trigger_DDL_View 已禁止工作时间对视图进行DDL的 create、drop、alter 操作'
　　

　　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/DatabaseName)[1]','nvarchar(max)') as DatabaseName,   
　　@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') as ObjectName,   
　　@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') as ObjectType,   
　　@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText   
　　

　　rollback   
　　end
　　
go
　　

　　
drop view v_Stu_Cou        
　　

　　启用和禁用数据库级别触发器：
　　

　　
enable trigger trigger_DDL_View on database;
　　

　　

　　
disable trigger trigger_DDL_View on database;  
　　

　　查看数据库级别的触发器及事件：
　　

　　
select * from sys.triggers
　　

　　

　　
select * from sys.trigger_events
　　

　　

　　
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
　　

　　EVENTDATA( ) 函数：可以在触发器内部（即 create 的 T-SQL 中）使用，当事件触发时，该函数返回一个 XML 数据类型，其中包含触发器的事件信息。
　　

　　select EVENTDATA();
　　

　　当返回 XML 类型的结果，可以点击进行查看：
　　

<EVENT_INSTANCE>　　
   <EventType>DROP_VIEW</EventType>　　-- 事件类型
　　
   <PostTime>2017-04-23T16:29:58.130</PostTime> -- 事件执行时间
　　
   <SPID>52</SPID>　　
　　
   <ServerName>DESKTOP-LQUB0OA</ServerName>　　-- 计算机名称
　　
   <LoginName>sa</LoginName>　　-- 登陆用户名
　　
   <UserName>dbo</UserName>　　-- 用户（即所有者）
　　
   <DatabaseName>Test</DatabaseName>　　-- 数据库名
　　
   <SchemaName>dbo</SchemaName>　　-- 所有者
　　
   <ObjectName>v_Stu_Cou</ObjectName>　　-- 对象名
　　
   <ObjectType>VIEW</ObjectType>　　--对象类型
　　
   <TSQLCommand>　　-- T-SQL 命令
　　
     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
　　
     <CommandText>drop view v_Stu_Cou        
　　
   </TSQLCommand>
　　
</EVENT_INSTANCE>
　　

　　更多关于 EVENTDATA() 函数请参考：
　　https://docs.microsoft.com/en-us/sql/t-sql/functions/eventdata-transact-sql
　　DDL 服务器（Server）级别触发器：
　　创建一个创建、修改、删除数据库的服务器级别触发器：
　　

　　
if exists(select * from sys.server_triggers where name='trigger_DDL_DB')
　　

　　
drop trigger trigger_DDL_DB on all server        
　　
go
　　
create trigger trigger_DDL_DB   
　　
on all server
　　
for create_database,drop_database,alter_database        
　　
as
　　declare @EventData xml
　　set @EventData=EVENTDATA();
　　

　　select EVENTDATA();
　　

　　select '触发器 trigger_DDL_DB 已禁止创建、修改、删除数据库操作'
　　

　　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
　　

　　
create database Demo        
　　on primary
　　(   
　　name = N'DemoDB_Data',   
　　filename = N'D:\DemoDB_Data.mdf',   
　　size = 5mb,   
　　maxsize = unlimited,
　　filegrowth = 5%   
　　)
　　log on
　　(
　　name=N'DemoDB_log',        
　　filename=N'D:\DemoDB_log.ldf',   
　　size = 2mb,           
　　maxsize = unlimited,        
　　filegrowth = 1%        
　　)
　　

　　启用和禁用服务器级别触发器：
　　

　　
enable trigger trigger_DDL_DB on all server;
　　

　　

　　
disable trigger trigger_DDL_DB on all server;  
　　

　　查看服务器级别触发器及事件：
　　

　　
select * from sys.server_triggers
　　

　　

　　
select * from sys.server_trigger_events
　　

　　

　　
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  
　　

　　参考：
　　http://www.cnblogs.com/qanholas/archive/2012/05/10/2494643.html
　　https://msdn.microsoft.com/zh-cn/library/ms190989(v=sql.100).aspx