金田贵媳,5xoy,中华龙飞
如果你有对触发器和事务的概念,有些了解,这篇文章,对你来说会是很简单,或能让你更进一步的了解触发器里面的一些故事,和触发器中事务个故事。在这边文章里面,我不会从触发器和事务的概念去讲述,而是从常见的两种触发器类型(dml触发器 & ddl触发器)和after触发器 & instead of 触发器的应用不同,开始说起它们,然后是说与事务有关的故事。如果,你有什么建议和意见,都可以通过文章后面的回复与我沟通,或者通过e-mail方式,与 我交流;我的email地址是:glal@163.com
在下面的内容,用到一些sql server 触发器和事务的一些术语,如果有些不明白的地方,可以查阅msdn资料库,或sql server本地帮助文档:
dml触发器(dml triggers) ddl触发器(ddl triggers) 事务模式(transaction modes) 显式事务(explicit transactions) 自动提交事务(autocommit transactions) 隐式事务(implicit transactions) 批范围的事务(batch-scoped transactions)after触发器 vs instead of触发器
after 触发器将在处理触发操作(insert、update 或 delete)、instead of 触发器和约束之后激发。instead of是将在处理约束前激发,以替代触发操作。下面两张图描述了after触发器和instead of触发器的执行先后顺序。
图1 图2
左边的图1,描述了after触发器执行顺序情况,我在这里通过一个简单的例子来说明after触发器的执行顺序,以便能加深对左图1 after触发器的理解。
先创建表contact
use tempdb
go
if object_id('contact') is not null
drop table contact
go
create table contact
(
id int primary key identity(1,1),
name nvarchar(50),
sex nchar(2) check(sex in(n'f',n'm')) default('m')
)
go
再创建after触发器tr_contact
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact after insert
as
select name,sex from inserted /*显示inserted表的内容,用来判断触发器执行的先后顺序*/
go
然后insert数据,判断after触发器的执行顺序
use tempdb
go
insert into contact (name,sex) values ('bill','u')
go
这里,在没有运行insert语句之前,我们可以判断,执行insert过程会触发check错误,因为字段sex的值必须是”f” or “m”,而这里将要插入的是”u”.好了,再来看运行insert语句后的情况。
本例子,只看到引发check约束冲突的错误,而无法看到inserted表的数据,说明一点就是,引起check约束之前,不会引发after触发器tr_contact的操作。这就验证了图1的after触发器执行顺序情况。
好了,接下来,我们再测试instead of触发器 图2的情况;我使用上边建好的测试表contact来举例。
先修改触发器tr_contact内容,
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact instead of insert
as
print '触发器作代替执行操作'
insert into contact (name,sex) select name,sex from inserted /*代替触发器外面的insert行为*/
go
再insert数据,观察sql server执行后的提示信息,
use tempdb
go
insert into contact (name,sex) values ('bill','u')
go
这里,看到,先是触发器操作,再是check约束处理。本例中,在触发器里面使用一条insert的语句来描述触发器的代替执行操作,这sql语句通过select表inserted得到触发器外面insert内容。当sql server执行到触发器里面的insert语句,才会引起check约束处理.倘若,在触发器tr_contact没有insert的代替行为,那么就不会出现check约束处理错误的信息(注:没有check错误信息,并不表示没有作check处理)。修改上边的触发器tr_contact内容,做个简易的验证.
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact instead of insert
as
print '触发器作代替执行操作'
go
use tempdb
go
insert into contact (name,sex) values ('bill','u')
go
select * from contact
可以看到,instead of 触发器tr_contact内容没有insert的sql语句,不会引发check处理错误,而且检查insert动作后的结果,发现表contact也没有之前我们insert的数据。这些足够验证了instead of触发器的执行先后顺序和代替执行操作。
dml 触发器 vs ddl 触发器
dml 触发器在 insert、update 和 delete 语句上操作,可以作为after 触发器 和 instead of 触发器。
ddl 触发器对 create、alter、drop 和其他 ddl 语句以及执行 ddl 式操作的存储过程执行操作,只可作为after触发器,不能instead of触发器。
前面的内容,有描述dml触发器中的after & instead of触发器内容,下面直接来看ddl的操作顺序:
图3.
从图3.可以知道,在ddl触发器中,是没有创建inserted & deleted过程的,我们通过简单的例子去测试下。
创建一个服务器范围内的ddl触发器,检查有没有inserted 表,
use master
go
if exists(select 1 from sys.server_triggers where name='tr_createdatabase')
drop trigger tr_createdatabase on all server
go
create trigger tr_createdatabase on all server after create_database
as
select * from inserted
go
执行创建数据库sql语句,
use master
go
create database mydatabase on primary
(name='mydatabase_data',filename='e:\data\sql2008de01\mydatabase_data.mdf') log on
(name='mydatabase_log',filename='e:\data\sql2008de01\mydatabase_log.ldf')
go
返回错误信息,
使用上边相同的方法,我们验证ddl触发器中,不会创建deleted表;是否创建deleted & inserted,也可以认为是ddl触发器与dml触发器不同之处。在dll触发器与dml触发器不同的一个重要特征是作用域,dml触发器只能应用在数据库层(database level)的表和视图上,而ddl触发器应用于数据库层(database level)和服务器层(server level);ddl触发器的作用域取决于事件。下面简单描述下事件组的内容。
数据库层事件主要包含:
服务器层事件主要包含:
触发器和事务的故事
创建一个表contacthist,用于对表contact作update or delete操作时,把操作前的数据insert到表contacthist中。
use tempdb
go
if object_id('contacthist') is not null
drop table contacthist
go
create table contacthist
(
id int primary key identity(1,1),
contactid int,
name nvarchar(50),
sex nchar(2),
actiontype nvarchar(10) check(actiontype in('update','delete')),
lastupdatedate datetime default(getdate())
)
go
修改触发器tr_contact内容,
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact after update,delete
as
insert into contacthist(contactid,name,sex)
select id,name,sex from deleted
rollback tran
begin tran
go
测试数据,
use tempdb
go
insert into contact (name,sex) values ('bill','f')
go
--update
update contact
set sex='m'
where name='bill'
go
select * from contact
select * from contacthist
go
测试结果:
从上边的测试情况,看出,update contact触发tr_contact触发器操作,触发器里面的rollback tran 动作导致了触发器外面的update语句执行回滚,而rollback tran 语句后面的begin tran语句,主要是应用于保持整个事务的完整性。为了更能理解这一过程,我模拟了一个触发器中的事务开始结束过程。
图4.
在sql server 2005 和 sql server 2008上面,可以看到如图4.的效果。在低版本的sql server上,可能会出现错误提示情况,不管如何,在触发器外面,sql server都会rollback tran。下面我做个错误提示的例子。
修改触发器tr_contact内容
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact after update,delete
as
insert into contacthist(contactid,name,sex)
select id,name,sex from deleted
rollback tran
--begin tran
go
重新执行update操作,
use tempdb
go
update contact
set sex='m'
where name='bill'
go
select @@trancount
go
select * from contact
select * from contacthist
go
在触发器里面没有begin tran语句动作,触发器外面也能回滚操作。这里我们可以通过查询表数据和@@trancount来判断。
其实,上面的例子,update语句,是以自动提交事务(autocommit transactions)模式 开始执行的,触发器里rollback tran后面,不管有没有begin tran ,最后都会事务都会交回给sql server自动提交事务管理。当然,在dml触发器中,你可以使用显式事务(explicit transactions),或开启隐式事务(implicit transactions) 来控制,当然你也可以应用于批范围的事务(batch-scoped transactions) 中。这里,我通过开启隐式事务(implicit transactions) 的例子来说,触发器与事务的关系。
修改触发器tr_contact的内容,
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact after update,delete
as
print n'触发器里insert 前,@@trancount='+rtrim(@@trancount)
insert into contacthist(contactid,name,sex)
select id,name,sex from deleted
print n'触发器里insert后,rollback tran 前,@@trancount='+rtrim(@@trancount)
rollback tran
print n'触发器里rollback tran 后,@@trancount='+rtrim(@@trancount)
begin tran
go
开启隐式事务(implicit transactions) 来测试,
use tempdb
go
set implicit_transactions on /**/
go
print n'update contact前,@@trancount='+rtrim(@@trancount)
update contact
set sex='m'
where name='bill'
print n'update contact后,@@trancount='+rtrim(@@trancount)
rollback tran
print n'触发器外面rollback tran 后,@@trancount='+rtrim(@@trancount)
go
set implicit_transactions off /**/
go
go
select * from contact
select * from contacthist
go
这里,你是否发现一个很有意思的问题,在触发器理,执行insert contacthist之前,@@trancount=1,执行insert后,@@trancount还是为1,触发器外面update contact后,@@trancount就变成了2,。这里可以理解成,你在触发器里面,发出一个begin tran,那么sql server 就会创建一个嵌套事务。当你在触发器里面,在rollback tran后面屏蔽掉begin tran,就会出现错误3609,如,
use tempdb
go
if exists(select 1 from sys.triggers where name='tr_contact')
drop trigger tr_contact
go
create trigger tr_contact on contact after update,delete
as
print n'触发器里insert 前,@@trancount='+rtrim(@@trancount)
insert into contacthist(contactid,name,sex)
select id,name,sex from deleted
print n'触发器里insert后,rollback tran 前,@@trancount='+rtrim(@@trancount)
rollback tran
print n'触发器里rollback tran 后,@@trancount='+rtrim(@@trancount)
go
这里,可以看到事务在触发器中rollback,又没有开启新的事务,导致整个批处理就中止,不会继续执行触发器外面的rollback tran操作。倘若,你在触发器中使用begin tran …… commit tran格式,那么触发器commit tran不会影响到外面的事务;下面描述三种常见触发器中事务的情况:
图5. 图6. 图7.
图5.描述在触发器中含有begin tran …… commit tran的情况,
图6.描述在触发器中含有save tran savepoint_name …… rollback tran savepoint_name 的情况,触发器中的rollback tran 只会回滚指定的保存点,不会影响到触发器外面的commit tran or rollback tran操作。
图7.描述在触发器中含有rollback tran的情况,不管触发器里面有没有begin tran,都会出现错误3609,中止批处理。
注:ddl触发器操作可以触发器中回滚操作,可以使用命令如rollback,但严重错误可能会导致整个事务自动回滚。不能回滚发生在 ddl 触发器正文内的 alter database事件。在触发器中使用rollback … begin tran 可能会导致意想不到的结果,在没有确认和测试情况下,请不要随便在触发器中直接使用rollback …begin tran处理方式.特别是create database事件,在sql server 2008和sql server 2005环境下,产生的结果不同。
rollback …begin tran情况:
create trigger ….
as
……
rollback
begin tran
end
小结
回顾前文至后文,从after触发器vsinstead of 触发器,说到dml触发器 vs ddl触发器,再到触发器中事务的故事。也许有些地方描述的有些模糊,有些地方只有一笔带过;你在测试代码过程中,可能发现有些地方与这里测试的情况不同,那可能是因为sql server版本的不同,导致一些测试结果不同。无论如何,只要你感觉对你了解触发器,有些帮助,就ok了。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论