【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
