滨州网站建设九鲁,汽车公司网站建设,seo如何提升排名收录,免费建筑设计软件引述 首先#xff0c; 说下我写篇文章的目的#xff0c;我希望能把我对触发器的理解#xff0c;分享出来与你一起学习。如果你有对触发器和事务的概念#xff0c;有些了解#xff0c;这篇文章#xff0c;对你来说会是很简单#xff0c;或能让你更进一步的了解触发器里面… 引述 首先 说下我写篇文章的目的我希望能把我对触发器的理解分享出来与你一起学习。如果你有对触发器和事务的概念有些了解这篇文章对你来说会是很简单或能让你更进一步的了解触发器里面的一些故事和触发器中事务个故事。在这边文章里面我不会从触发器和事务的概念去讲述而是从常见的两种触发器类型(DML触发器 DDL触发器)和After触发器 Instead Of 触发器的应用不同开始说起它们然后是说与事务有关的故事。如果你有什么建议和意见都可以通过文章后面的回复与我沟通或者通过E-Mail方式与 我交流我的Email地址是glal163.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(NF,NM)) Default(M) ) Go 再创建After触发器tr_Contact use tempdb Go If Exists(Select 1 From sys.triggers Where nametr_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 nametr_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 nametr_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 nametr_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 (NameMyDataBase_Data,FilenameE:\DATA\SQL2008DE01\MyDataBase_Data.mdf) Log On (NameMyDataBase_Log,FilenameE:\DATA\SQL2008DE01\MyDataBase_Log.ldf) Go 返回错误信息 使用上边相同的方法我们验证DDL触发器中不会创建Deleted表是否创建Deleted Inserted,也可以认为是DDL触发器与DML触发器不同之处。在DLL触发器与DML触发器不同的一个重要特征是作用域DML触发器只能应用在数据库层(Database Level)的表和视图上而DDL触发器应用于数据库层(Database Level)和服务器层(Server Level)DDL触发器的作用域取决于事件。下面简单描述下事件组的内容。 数据库层事件主要包含: DDL Table events: Create table, Alter table, Drop tableDDL view events : Create view, Alter view, Drop viewDDL trigger events :Create trigger, Drop trigger, Alter triggerDDL synonym events: Create synonym, drop synonymDDL Index events: Create index, Alter index, Drop IndexDDL Database level security events: Create User, Drop user, Alter userCreate role, Drop role, Alter roleCreate application role, Drop application role, Alter Application roleCreate Schema, Drop Schema, Alter SchemaGrant database access, Revoke database access, Deny Database accessDDL Service broker events: Create Message type, Alter Message type, Drop Message typeCreate contract, Drop contract, Alter contractCreate Service, Alter service, Drop ServiceCreate route, Drop route, Alter route服务器层事件主要包含 Create Database, Drop DatabaseCreate Login, Drop Login, Alter Login触发器和事务的故事 在前面的几个例子中如DML触发器例子Insert 语句执行后因为触发器操作 或 Check处理错误没有把数据真正的插入到表Contact中。其实当执行触发器时触发器的操作好像有一个未完成的事务在起作用。 通过几个例子来讲解触发器和事务的故事。 创建一个表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 nametr_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 SexM Where NameBill 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 nametr_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 SexM Where NameBill 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 nametr_Contact) Drop Trigger tr_Contact Go Create Trigger tr_Contact On Contact After Update,Delete As Print N触发器里Insert 前TrancountRtrim(Trancount) Insert Into ContactHIST(ContactID,Name,Sex) Select ID,Name,Sex From deleted Print N触发器里Insert后Rollback Tran 前TrancountRtrim(Trancount) Rollback Tran Print N触发器里Rollback Tran 后TrancountRtrim(Trancount) Begin Tran Go 开启隐式事务(Implicit Transactions) 来测试 use tempdb Go Set Implicit_transactions On /**/ Go Print NUpdate Contact前TrancountRtrim(Trancount) Update Contact Set SexM Where NameBill Print NUpdate Contact后TrancountRtrim(Trancount) Rollback Tran Print N触发器外面Rollback Tran 后TrancountRtrim(Trancount) Go Set Implicit_transactions Off /**/ Go Go Select * From Contact Select * From ContactHIST Go 这里你是否发现一个很有意思的问题在触发器理执行Insert ContactHIST之前Trancount1,执行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 nametr_Contact) Drop Trigger tr_Contact Go Create Trigger tr_Contact On Contact After Update,Delete As Print N触发器里Insert 前TrancountRtrim(Trancount) Insert Into ContactHIST(ContactID,Name,Sex) Select ID,Name,Sex From deleted Print N触发器里Insert后Rollback Tran 前TrancountRtrim(Trancount) Rollback Tran Print N触发器里Rollback Tran 后TrancountRtrim(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了。 转载于:https://www.cnblogs.com/mo-beifeng/archive/2011/05/16/2048137.html
