首页 网页制作网络编程脚本专栏数据库网站运营网络安全平面设计CMS教程

SQL Server触发器及触发器中的事务学习

所属分类： 数据库 / MsSql 阅读数： 585
收藏 0 赞 0 分享
如果你有对触发器和事务的概念，有些了解，这篇文章，对你来说会是很简单，或能让你更进一步的了解触发器里面的一些故事，和触发器中事务个故事。在这边文章里面，我不会从触发器和事务的概念去讲述，而是从常见的两种触发器类型(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触发器的执行先后顺序。

     image      
image 

     图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语句后的情况。

image

本例子，只看到引发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

image  

这里，看到，先是触发器操作，再是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

imageimage

可以看到，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的操作顺序：

     image

     图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

返回错误信息，

image

使用上边相同的方法，我们验证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 table DDL view events : Create view, Alter view, Drop view DDL trigger events :Create trigger, Drop trigger, Alter trigger DDL synonym events: Create synonym, drop synonym DDL Index events: Create index, Alter index, Drop Index DDL Database level security events: Create User, Drop user, Alter user Create role, Drop role, Alter role Create application role, Drop application role, Alter Application role Create Schema, Drop Schema, Alter Schema Grant database access, Revoke database access, Deny Database access DDL Service broker events: Create Message type, Alter Message type, Drop Message type Create contract, Drop contract, Alter contract Create Service, Alter service, Drop Service Create route, Drop route, Alter route

服务器层事件主要包含：

    Create Database, Drop Database Create 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 name='tr_Contact')
 Drop Trigger tr_Contact 
Go
Create Trigger tr_Contact On Contact After Update,Delete
As
                
更多精彩内容其他人还在看

                
SQL Server正确删除Windows认证用户的方法
这篇文章主要给大家介绍了关于SQL Server正确删除Windows认证用户的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用SQL Server具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SQL Server查看login所授予的具体权限问题
在SQL Server数据库中如何查看一个登录名(login)的具体权限呢，下面脚本之家小编给大家带来了SQL Server查看login所授予的具体权限问题，感兴趣的朋友一起看看吧

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
Mysql8.0.17安装教程【推荐】
本文通过图文并茂的形式给大家介绍了Mysql8.0.17安装,非常不错，具有一定的参考借鉴价值，需要的朋友可以参考下

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
C# ling to sql 取多条记录最大时间
这篇文章主要介绍了C# ling to sql 取多条记录最大时间,文中通过实例代码给大家介绍了sql 查询相同记录下日期最大的 一条，代码简单易懂，需要的朋友可以参考下

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
sql server编写archive通用模板脚本实现自动分批删除数据
这篇文章主要介绍了sql server编写archive通用模板脚本实现自动分批删除数据,需要的朋友可以参考下

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SQL Server怎么找出一个表包含的页信息（Page)
这篇文章主要给大家介绍了关于SQL Server是如何找出一个表包含的页信息（Page)的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用SQL Server具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SQLyog连接MySQL8.0报2058错误的完美解决方法
这篇文章主要介绍了SQLyog连接MySQL8.0报2058错误的完美解决方法，本文图文并茂给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SQL SERVER日志进行收缩的图文教程
这篇文章主要给大家介绍了关于SQL SERVER日志进行收缩的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者使用SQL SERVER具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SqlServer中批量update语句
我现在想把S_USER表中的ACCOUNT批量修改成S_PERSON的ACCOUNT，那么就可以参考下面的语句，要找到对应的字段

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
SqlServer AS的用法
本篇文章简要分析，在SQL SERVER中，AS的基本用法,需要的朋友可以参考下

                    
                    
                        
                    收藏 0
                        
                    赞 0
                        
                    分享
                查看更多

网络赚钱

更多

站长故事

更多
建站极客
合作伙伴
在线工具
建站极客移动版
聚合全网技术文章，根据你的阅读喜好进行个性推荐
© 2012 - 2020 www.zhanzhang360.cn Some Rights Reserved.
沪ICP备13040166号-22