【SQL Server学习笔记】DML触发器、DDL触发器

触发器包含：dml触发器和ddl触发器。

dml触发器包含了用于对表或视图的insert、update、delete操作做出响应的T-SQL代码，而ddl触发器对服务器或数据库事件做出响应而不是数据修改。

触发器能够自动响应某种行为，所以对于必须对某种行为做出业务级别响应的情况，触发器很合适。

在用触发器时，需要注意的：

1、触发器通常比较隐蔽，很容易被忘记，在检查性能或逻辑问题的时候，经常会忘记触发器是在后台执行的，要确保在文档中记录了触发器。

2、如果所有的数据修改流程都通过存储过程完成，那么尽可能不要使用触发器。

3、始终需要保证性能，确保能快速执行且没有bug。长时间运行的触发器会严重减慢数据修改操作，所有在数据修改比较频繁的数据库中使用触发器需要特别小心。

4、不记录日志的更新不会引起dml触发器的触发，如：writetext，truncate table、批量导入操作。

5、约束通常比dml触发器运行更快，因此如果约束能满足业务的需要，则使用约束来代替。由于after触发器是在数据修改之后触发的，所以不能防止违反约束。

6、不允许在触发器中使用select语句来返回结果集。

create table dbo.t
	(vid int not null primary key,
	 v varchar(100) ,
	 vv varchar(10)
	)
	
	
select * into dbo.t_insert
from dbo.t

select * into dbo.t_update
from dbo.t

select * into dbo.t_delete
from dbo.t


insert into dbo.t(vid,v,vv)
values(1,'a','aaa'),
      (2,'b','bbb'),
      (3,'c','ccc'),
      (4,'d','ddd'),
      (5,'e','eee')


--创建after dml触发器
create trigger dbo.t_after
on dbo.t
after insert,delete
as

insert into dbo.t_insert
select *
from inserted

insert into dbo.t_delete
select *
from deleted

go

--触发
insert into dbo.t
values(6,'f','ffff')


--发现已经添加到表中
select * from dbo.t_insert

--1.创建instead of触发器
create trigger dbo.t_instead_of
on dbo.t
with encryption
instead of insert 
           not for replication
as

declare @v varchar(100)
set @v = ''

select @v = V 
from inserted

if (@v = 'a')
   rollback    --回滚

/*=========================================
引用插入的列只能在select语句中使用,
而不能单独引用：

if(inserted.v = 'a')
  rollback
 
无法绑定由多个部分组成的标识符 "inserted.v"
===========================================*/
go


--事务在触发器中结束。批处理已中止。
insert into dbo.t
values(7,'a','aaa')



--2.修改触发器，根据修改列控制触发器
alter trigger dbo.t_instead_of
on dbo.t
with encryption
instead of insert 
           not for replication
as

declare @v varchar(100)
set @v = ''

select @v = V 
from inserted

--如果在v列上执行insert或update，那么update(v)返回true
if (@v = 'a' or UPDATE(v))
   rollback    --回滚

/*=========================================
引用插入的列只能在select语句中使用,
而不能单独引用：

if(inserted.v = 'a')
  rollback
 
无法绑定由多个部分组成的标识符 "inserted.v"
===========================================*/
go


--由于更新了v列,所以事务在触发器中结束。批处理已中止。
insert into dbo.t
values(7,'g','g')

--1.创建数据库级别触发器
create trigger wcc
on database
for create_table
as

raiserror( 'A error occur,please retry again!',16,1)
rollback 
go

/*=======================================
消息 50000，级别 16，状态 1，过程 wcc,
A error occur,please retry again!
消息 3609，级别 16，状态 2，第 1 行
事务在触发器中结束。批处理已中止。
=========================================*/
create table www(vid int not null)



--2.1在master数据库中建立服务器级别跟踪表
use master
go

create table server_eventdata
	(eventdata xml,
     principal_user nvarchar(100),
     login_user nvarchar(100)
    )
    
    
--2.2建立服务器级别触发器
create trigger gyy_server
on all server
for create_table,drop_table,create_index
as

insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go

--2.3会触发服务器级别触发器
create table www(vid int not null)

--查看记录的事件
select * from server_eventdata

触发器元数据、管理触发器

--1.dml触发器元数据
select o.name,
       t.name,
       t.parent_class_desc,       --对象或列
       
       t.type,               
       t.type_desc,               --触发器
       is_not_for_replication,  --在对表进行复制修改时不执行触发器
       is_instead_of_trigger,   --是否是instead of触发器
       
       s.definition
from sys.triggers t
inner join sys.objects o
        on t.parent_id = o.object_id
inner join sys.sql_modules s
        on s.object_id = t.object_id


--2.数据库级别ddl触发器元数据
select t.name,
       
       T.parent_class,
       T.parent_class_desc,
       T.type_desc,
       
       S.definition
from sys.triggers t
inner join sys.sql_modules s
        on s.object_id = t.object_id
where parent_id =0
      and parent_class_desc ='DATABASE'


--3.服务器级别ddl触发器元数据
SELECT ST.name,
       parent_class,
       parent_class_desc,
       type_desc,
       
       SSM.definition
FROM SYS.server_triggers ST
INNER JOIN SYS.server_sql_modules SSM
        ON ST.object_id = SSM.OBJECT_ID
WHERE parent_class_desc = 'SERVER'



--4.1限制触发器嵌套,服务器范围的选项
use master
go

--禁止触发器嵌套
exec sp_configure 'nested triggers',0
reconfigure with override
go

--启用触发器嵌套
exec sp_configure 'nested triggers',1
reconfigure
go

--4.2控制触发器递归,数据库范围的选项
--允许递归,after触发器仍然受到32层嵌套的限制
alter database wcc
set recursive_triggers on

--禁止递归
alter database wcc
set revursive_triggers off

--查看数据库是否允许触发器递归
select is_recursive_triggers_on
from sys.databases


--5.1禁用某个表的某个触发器
disable trigger dbo.t_after
on dbo.t

--5.2禁用某个表的所有触发器
disable trigger all on dbo.t

--5.3禁用某个数据库的某个数据库触发器
disable trigger wcc on database

--5.4禁用某个数据库的所有数据库触发器
disable trigger all on database

--5.5禁用服务器上的某个触发器
disable trigger gyy_server on all server

--5.6禁用服务器上所有的服务器级别触发器
disable trigger all on all server

--5.7启用服务器上所有的服务器级别触发器
enable trigger all on all server

--5.8删除dml触发器
drop trigger dbo.t_after

--5.9删除ddl数据库级别触发器
drop trigger wc on database

--5.10删除ddl服务器级别触发器
drop trigger gyy_server on all server


--6.设置触发器触发的次序
create trigger dbo.tt_1
on dbo.t
after insert
as

print 'dbo.tt_1'
go


create trigger dbo.tt_2
on dbo.t
after insert
as

print 'dbo.tt_2'
go


create trigger dbo.tt_3
on dbo.t
after insert
as

print 'dbo.tt_3'
go


--设置触发器触发的次序
exec sp_settriggerorder 
	@triggername = 'tt_1', --触发器名称 
	@order ='first',       --指定的次序
	@stmttype = 'insert'  --触发器类型

exec sp_settriggerorder
	@triggername = 'tt_2',
	@order = 'last',
	@stmttype = 'insert'
	
--触发多个触发器
insert into dbo.t
values(10,'w','www')

/*======================================
输出消息：

		dbo.tt_1
		dbo.tt_3
		dbo.tt_2
========================================*/

如果有10个表，需要union all这10个表的数据来查询，那么通过建立一个视图，然后要对视图进行插入操作，那么必须有几个必要的条件：

1.每个表都有主键

2.每个表必须有check约束，来指定任何一条数据，到底要插入到那个表中。

 

下面为ddl触发器的实例：

--2.1在master数据库中建立服务器级别跟踪表
use master
go

create table server_eventdata
	(eventdata xml,
     principal_user nvarchar(100),
     login_user nvarchar(100)
    )
go    
/*
select * from sys.trigger_event_types
where type_name like '%grant%' or
      type_name like '%deny%' or
      type_name like '%revoke%'
*/

    
    
--2.2建立服务器级别触发器

create trigger gyy_server
on all server
for GRANT_SERVER,
    DENY_SERVER,
    REVOKE_SERVER,
    GRANT_DATABASE,
    DENY_DATABASE,
    REVOKE_DATABASE
as

insert into server_eventdata
select EVENTDATA(),USER,SUSER_NAME()
go


--2.3
create database wc
go

use wc
go

create table dbo.wc_table(v int)

insert into dbo.wc_table values(1)
go


use wc
go

grant select on wc.dbo.wc_table to public

go

--查看记录的事件
select EVENTDATA,
       eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') as '事件类型',
       eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)') as 'sql授权语句',
       
       '登录名' + eventdata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') + 
       '用户名' + eventdata.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)') + ',授予者'+
       eventdata.value('(/EVENT_INSTANCE/Grantor)[1]','nvarchar(100)') + ' 把类型为：' +
       eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)') + '的对象' +
       eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') + '.' +
       eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '.' +
       eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '的' +
       eventdata.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','nvarchar(100)') +'权限授予给' +
       
       eventdata.value('(/EVENT_INSTANCE/Grantees)[1]','nvarchar(100)')            
                   
from master.dbo.server_eventdata t

下面是一段引用自邹建的代码，通过链接服务器+触发器，实现数据同步的代码，很有借鉴作用：

/*  
  作者:邹建  
  */  
   
  /*--同步两个数据库的示例  
   
  有数据  
  srv1.库名..author有字段:id,name,phone,  
  srv2.库名..author有字段:id,name,telphone,adress  
   
  要求：  
  srv1.库名..author增加记录则srv1.库名..author记录增加  
  srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新  
  --*/  
   
  --大致的处理步骤  
  --1.在   srv1   上创建连接服务器,以便在   srv1   中操作   srv2,实现同步  
  exec   sp_addlinkedserver     'srv2','','SQLOLEDB','srv2的sql实例名或ip'  
  exec   sp_addlinkedsrvlogin   'srv2','false',null,'用户名','密码'  
  go  
   
  --2.在   srv1   和   srv2   这两台电脑中,启动   msdtc(分布式事务处理服务),并且设置为自动启动  
  我的电脑--控制面板--管理工具--服务--右键   Distributed   Transaction   Coordinator--属性--启动--并将启动类型设置为自动启动  
  go  
   
   
  --3.实现同步处理  
   
  --a.在srv1..author中创建触发器,实现数据即时同步  
  --新增同步  
  create   trigger   tr_insert_author   on   author  
  for   insert  
  as  
  set   xact_abort   on  
  insert   srv2.库名.dbo.author(id,name,telphone)  
  select   id,name,telphone   from   inserted  
  go  
   
  --修改同步  
  create   trigger   tr_update_author   on   author  
  for   update  
  as  
  set   xact_abort   on  
  update   b   set   name=i.name,telphone=i.telphone  
  from   srv2.库名.dbo.author   b,inserted   i  
  where   b.id=i.id  
  go  
   
  --删除同步  
  create   trigger   tr_delete_author   on   author  
  for   delete  
  as  
  set   xact_abort   on  
  delete   b    
  from   srv2.库名.dbo.author   b,deleted   d  
  where   b.id=d.id  
  go