SQL Server DDL触发器
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)','nvarchar(max)') as EventType,
@EventData.value('(/EVENT_INSTANCE/PostTime)','nvarchar(max)') as PostTime,
@EventData.value('(/EVENT_INSTANCE/DatabaseName)','nvarchar(max)') as DatabaseName,
@EventData.value('(/EVENT_INSTANCE/ObjectName)','nvarchar(max)') as ObjectName,
@EventData.value('(/EVENT_INSTANCE/ObjectType)','nvarchar(max)') as ObjectType,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','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)','nvarchar(max)') as EventType,
@EventData.value('(/EVENT_INSTANCE/PostTime)','nvarchar(max)') as PostTime,
@EventData.value('(/EVENT_INSTANCE/ServerName)','nvarchar(max)') as ServerName,
@EventData.value('(/EVENT_INSTANCE/DatabaseName)','nvarchar(max)') as DatabaseName,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','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
页:
[1]