SQL Server DDL触发器

-- 判断名为 trigger_DDL_Table 的数据库级别的触发器是否存在
if exists(select * from sys.triggers where parent_class=0 and name=‘trigger_DDL_Table‘)

drop trigger trigger_DDL_Table on database        --删除名为 trigger_DDL_Table 的数据库级别的触发器
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 数据库级别触发器：

-- 判断名为 trigger_DDL_View 的数据库级别的触发器是否存在
if exists(select * from sys.triggers where parent_class=0 and name=‘trigger_DDL_View‘)

drop trigger trigger_DDL_View on database        --删除名为 trigger_DDL_View 的数据库级别的触发器
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        -- 删除视图

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

-- 启用名为 trigger_DDL_View 的数据库级别触发器  
enable trigger trigger_DDL_View on database;

-- 禁用名为 trigger_DDL_View 的数据库级别触发器 
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 类型的结果，可以点击进行查看：

 1 <EVENT_INSTANCE>
 2   <EventType>DROP_VIEW</EventType>　　-- 事件类型
 3   <PostTime>2017-04-23T16:29:58.130</PostTime> -- 事件执行时间
 4   <SPID>52</SPID>　　
 5   <ServerName>DESKTOP-LQUB0OA</ServerName>　　-- 计算机名称
 6   <LoginName>sa</LoginName>　　-- 登陆用户名
 7   <UserName>dbo</UserName>　　-- 用户（即所有者）
 8   <DatabaseName>Test</DatabaseName>　　-- 数据库名
 9   <SchemaName>dbo</SchemaName>　　-- 所有者
10   <ObjectName>v_Stu_Cou</ObjectName>　　-- 对象名
11   <ObjectType>VIEW</ObjectType>　　--对象类型
12   <TSQLCommand>　　-- T-SQL 命令
13     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
14     <CommandText>drop view v_Stu_Cou        -- 删除视图</CommandText>　　-- 命令文本
15   </TSQLCommand>
16 </EVENT_INSTANCE>

更多关于 EVENTDATA() 函数请参考：

https://docs.microsoft.com/en-us/sql/t-sql/functions/eventdata-transact-sql

DDL 服务器（Server）级别触发器：

创建一个创建、修改、删除数据库的服务器级别触发器：

-- 判断名为 trigger_DDL_DB 的服务器级别的触发器是否存在，注意这里使用的是 sys.server_triggers 这个视图
if exists(select * from sys.server_triggers where name=‘trigger_DDL_DB‘)

drop trigger trigger_DDL_DB on all server        --删除名为 trigger_DDL_DB 的服务器级别的触发器
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%        
     )

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

-- 启用名为 trigger_DDL_DB 的服务器级别触发器  
enable trigger trigger_DDL_DB on all server;

-- 禁用名为 trigger_DDL_DB 的服务器级别触发器 
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

SQL Server DDL触发器

标签：login   archive   mod   --   growth   www.   sof   basename   and