sqlserver 触发器教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

主类别表名：Navtion_TopSubject 主键fTopID Char(36)
次类别表名：Navtion_NodeSubject 外键同上
内容表名：tText 外键同上

 
// ------------- 代码开始 -------------------- 
CREATE TRIGGER [RemoveTopAndNodeText] ON [dbo].[Navtion_TopSubject] 
INSTEAD OF DELETE 
AS 
/* 定义触发器使用的变量 */ 
DECLARE 
@fTopID Char(36), 
@fNodeCount Int, 
@fTextCount Int, 
@fTopName VarChar 

/* 把传送的需要删除的fTopID键值赋值给@fTopID变量 */ 
/* 开始事务 */ 
BEGIN TRAN Remove_TopSubject 
Set @fTopID = (Select fTopID From deleted) 
Set @fTopName = (Select fTopName From deleted) 
/* 保存删除前保存点，防止出错 */ 
Save Tran my_Save1 
/* 首先判断子类表NodeSubject中是否有所属内容 */ 
Set @fNodeCount = (Select Count(*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @fTopID) 
If @fNodeCount > 0 
Begin 
/* 判断内容表tText是否有所属内容 */ 
Set @fTextCount = (Select Count(*) From tText Where tText.fTopID = @fTopID) 
If @fTextCount > 0 
Begin 
Delete From tText Where tText.fTopID = @fTopID 
Delete From Navtion_NodeSubject Where fTopID = @fTopID 
Delete From Navtion_TopSubject Where fTopID = @fTopID 
End 
Else 
Begin 
Delete From Navtion_NodeSubject Where fTopID = @fTopID 
Delete From Navtion_TopSubject Where fTopID = @fTopID 
End 
End 
Else 
Begin 
Delete From Navtion_TopSubject Where fTopID = @fTopID 
End 
If @@Error = 0 
Commit Transaction 
Else 
Begin 
Rollback Transaction my_Save1 
Raiserror('删除出现错误，记录：%s及其所属内容没有被删除。',16,1,@fTopName) 
End 
//------------------代码结束--------------------- 

一、背景
这是我写的一个文章处理系统，分类级别为两级，就是说有两级目录类似这种：
新闻
-->国内新闻
-->国际新闻
教程
-->Asp教程
-->C#教程
-->Jsp教程
下载
-->工具下载
-->源代码下载
每个文章都属于上面的目录下的一个。
这样看，数据库内必须有三个表：主类别表、次类别表、文章内容表。而且，我为了保证数据的完整性，使用了关系（懂SQL Server的知道），这样，假设新闻类下有子类并且每个子类下有文章的话，使用Deltee语句删除类将出现错误，因为违反了数据完整性约束，把类别删除后所属的记录将变为死记录。所以删除主表的记录必须保证这个类别所包含的子类别和内容全部删除。我写的这个触发器的作用就是删除主表类别的触发器，可以在主表类别下有内容的情况下删除主表的记录。
二、数据库结构
1.主类别表
表名：Navtion_TopSubject
主健：fTopID 数据类型：Char 大小：36
类别名： fTopName 数据类型：VarChar 大小：30
2. 次类别表
表名：Navtion_NodeSubject
主键：fNodeID 数据类型：Char 大小：36
外键：fTopID 数据类型：Char 大小：36 （表示此记录所属的主类别）
类别名：fNodeName 数据类型：VarChar 大小：30
3. 内容表：
表名：tText
主键：fID 数据类型：Char 大小：36
外键1：fTopID 指向主类别表表示所属的主类别
外键2：fNodeID 指向次类别表，表示所属的次类别
三、操作流程
1. 这个触发器放在主类别表Navtion_TopSubject中，触发条件是Delete语句，如果对这个表执行了删除命令的话，那么将触发此代码的运行。
2. 定义触发器接受传递过来的Delete语句，然后根据这个语句来执行代码。
3. 标准SQL删除代码如下：
DELETE From Navtion_TopSubject Where fYopID = 'aaa' （假设要删除的主类别是新闻，主键编号是aaa）
4. 这样我们就可以利用这个aaa来查找次类别表和内容表是否有所属的内容。
5. 利用deleted表，这个表是逻辑删除表，相当于Windows系统里面的回收站。Sql Server系统定义：如果一个表里面有触发器，那么任何操作都不直接进行，而是进行逻辑操作。这个操作在Inserted表（插入），Updated表（更新），Deleted表（删除）中进行。就拿Deleted表来说，传递到Sql Server系统中的Del命令，如果有触发器的话，Sql首先复制这个要删除的记录到Deleted表中（这个表是个临时表，只能给触发器用，触发器运行结束后自动删除），我们就可以用此代码：
Select fTopID From deleted
获得传递到Sql的表Navtion_TopSubject中记录为aaa的主键内容。
6. 把这个主键内容存放到变量中，根据这个变量找次类别表，如果没有内容，表示此记录没有任何次类别和文章，可以直接删除。
7. 如果次类别表中有内容，那么再用这个变量找内容表中属于aaa的内容，如果有，就删除，再删除次类别表的内容，如果没有，则直接删除次类别表的内容。
8. 当次类别表和内容表的记录全部删除干净后，删除主类别表的内容。
四、流程定义
先查找子类别的数量，赋值给@NodeCount变量，然后判断
如果>0
则根据@fTopID找tText表的内容赋值给@fTextCount，如果>0则
首先删除tText表符合@fTopID内容的所有记录
然后删除子类别表Navtion_NodeSubject表中符合@fTopID的所有内容
最后删除主类别表Navtion_TopSubject中符合@fTopID的所有内容
否则
首先删除Navtion_NodeSubject子类别表中符合@fTopID的所有内容
然后删除主类别表Navtion_TopSubject中符合@fTopID的所有内容
否则
仅删除主类别表Navtion_TopSubject中符合@fTopID的所有内容
五、代码解释
CREATE TRIGGER [RemoveTopAndNodeText] ON [dbo].[Navtion_TopSubject]
INSTEAD OF DELETE
AS
上面的代码是建立触发器的Sql语句，内容：
CREATE TRIGGER T-SQL关键字，表示是一个触发器
[RemoveTopAndNodeText] 是触发器的名称
[dbo].[Navtion_TopSubject] 表示这个触发器属于的表的名称
INSTEAD OF 表示这个触发器将屏蔽传递进来的Sql命令，转向执行触发器的命令
（多说一句：除了INSTEAD OF关键字，还有 For关键字，表示这个触发器在执行完传递进来的Sql语句后执行触发器内容，一般用在Insert和Update中）
DELETE ：表示此触发器的触发条件是Delete命令，如果对这个表作删除操作，将执行这个触发器的代码。
As 表示下面的代码是触发器的代码：
-------------------------------------------------------------------
/* 定义触发器使用的变量 */
DECLARE
@fTopID Char,
@fNodeCount Int,
@fTextCount Int,
@fTopName VarChar
以上命令是定义触发器使用的变量，DECLARE 命令相当于VBScript的Dim命令，不过这个语句可以定义多变量，不用一个变量一个命令，变量之间用英文逗号分隔。
@fTopID 是变量名，SqlServer规定，在SqlServer中使用的变量名前面必须加入@字符。
变量后面是变量类型 Char是固定长度的字符串，Int是整数数字类型，VarChar是可变长度的字符串。
--------------------------------------------------------------------------
/* 把传送的需要删除的fTopID键值赋值给@fTopID变量 */
/* 开始事务 */
BEGIN TRAN Remove_TopSubject
Set @fTopID = (Select fTopID From deleted)

/* 保存删除前保存点，防止出错 */
Save Tran my_Save1
以上代码解释：
放在 /* 和 */之间的是程序注释，类似于Html里面的
BEGIN TRAN Remove_TopSubject 表示开始事务，其中RemoveTopSubject是事务名称。
事务就是保证操作成功的一种机制，如果在事务里面出错，那么事务将回滚，不会影响整个系统。
举个例子，如果在事务里面定义了3个操作a b c，分别是a 插入一条记录，b 删除一条记录，c 更新一条记录。程序开始执行，如果a 执行成功后开始操作b，b操作出现错误，那么事务开始回滚，插入记录的a将会取消，返回到没有执行这3个操作之前的状态。
Set @fTopID = (Select fTopID From deleted)
这个是Sql Server的赋值命令，把变量@fTopID的值保存成deleted表中fTopID字段的内容。
Set @fTopName = (Select fTopName From deleted)
同样的赋值命令，把要删除的主类别的名称赋值给@fTopName变量，在后面的出错语句用。
Save Tran my_Save1
Save Tran表示保存事务，如果发生错误，则可以用这个保存来恢复。类似于游戏里面的存盘文件。my_Save1是保存名，相当于存盘文件名。
接上：
Set @fNodeCount = (Select Count(*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @fTopID)
也是一个变量赋值语句，设定@fNodeCount变量保存的是要删除的主表记录（@fTopID变量的内容）所属的次类别表Navtion_NodeSubject中的记录的数量
根据主表记录查找其他表符合条件的这个语句可参考这个帖子：
http://www.dw-mx.com/forum/mb_forum/detail2.asp?f2_id=37&f3_id=9022&f3_name=笑望人生
--------------------------------------------------------------------
If @fNodeCount > 0（作个标记，一级判断）
开始判断，如果@fNodeCount大于0，表示欲删除的主类别记录包含有子类别，不能直接删除，必须先删除子类别。
但删除子类别必须删除子类别下包含的所有内容（文章）记录，所以我们必须判断是否有内容记录
-----------------------------------------------------------------
Begin （作个标记，一级Begin）
表示If下面执行的多条语句，不懂的可以参考没人性的FAQ帖子
--------------------------------------------------------------
Set @fTextCount = (Select Count(*) From tText Where tText.fTopID = @fTopID)
上面代码继续赋值，@TextCount表示欲删除的主类别记录所包含的所有的内容（文章）记录的数量
------------------------------------------
If @fTextCount > 0（标记，二级判断）
判断记录的数量，大于0表示有记录，这样必须先删除内容表的记录，再删除子类别表的记录，最后删除主类别表的记录，这样才不会出错。
Begin （标记，二级Beging 一）
---------------------------------------------------
Delete From tText Where tText.fTopID = @fTopID
Delete From Navtion_NodeSubject Where fTopID = @fTopID
Delete From Navtion_TopSubject Where fTopID = @fTopID
End（标记，二级Begin一的结束命令）
上面的语句很简单，首先删除tText表中fTopID符合@fTopID的记录（删除内容表）
然后删除Navtion_NodeSubject表符合@fTopID的记录（删除次类别表）
最后删除Navtion_TopSubject表符合@fTopID的记录（删除主类别表）
----------------------------------------------------
Else （标记，二级判断否则，表示@fTextCount=0，指内容表没有记录）
--------------------------------------------------------
Begin （标记，二级Begin二）
Delete From Navtion_NodeSubject Where fTopID = @fTopID
Delete From Navtion_TopSubject Where fTopID = @fTopID
End （标记，二级Begin二结束）
以上代码简单，tText表（内容表）没有记录，那么首先删除次类别表Navtion_NodeSubject中的记录，再删除主类别表Navtion_TopSubject中的记录。
------------------------------------
End （标记，二级Begin结束）
-------------------------------------
Else （标记：一级判断否则，表示次类别表没有内容@fNodeCount=0）
-------------------------------------------------------------
Begin
Delete From Navtion_TopSubject Where fTopID = @fTopID
End
上面代码直接删除主类别表Navtion_TopSubject的记录
-----------------------------------------------------
本来这个触发器已经结束，但是我们必须防止执行出错。所以如果出错，那么要回滚所有操作，并且向应用程序发送错误信息。

复制代码代码如下:

 
If @@Error = 0 
判断，是否出现错误 
@@Error是Sql Server的一个全局变量，保存上一个Sql命令是否出错，如果出错，@@Error=1 
如果是0，表示没有出错。 
------------------------------------------- 
Commit Transaction 
上面是没有出错的语句，表示事务提交，（可以看作是结束事务） 
如果前面有了Begin Tran开始事务的代码，必须在后面加上这个代码，否则事务不结束，数据库记录将被锁定，无法对数据库记录进行操作！ 
-------------------------------------------- 
Else 
@@Error变量大于0，表示出现错误 
---------------------------------- 
Begin 
Rollback Transaction my_Save1 
Raiserror('删除出现错误，记录：%s及其所属内容没有被删除。',16,1,@fTopName) 
End 

上面的代码是个语句块，表示如果上面的删除语句出错后怎么办
Rollback Transaction my_Save1
Rollback Transaction 是Sql Server的内部命令，表示回滚事务，就是以前的操作全部取消。my_Save1是上面保存的保存点的名称。
这个代码意思和玩游戏中没有过关，从存盘文件中恢复的道理一样。 :D
----------------------------------------------------------------------
Raiserror('删除出现错误，记录：%s及其所属内容没有被删除。',16,1,@fTopName)
Raiserror表示自定义错误信息，这个语句的作用是向使用Sql Server的应用程序发送信息。
如asp的代码，asp代码获得这个信息将显示在网页上。
16,1是错误的等级号码，这个我不做过多解释，有兴趣的人可以查看SQL Server联机丛书的
T-SQL语言参考。
%s是Reiserror命令的自定义变量 %s表示字符串变量，内容是后面的@fTopName的内容。
这个语句类似于以下VBScript代码（假设VBscript中有Raiserror命令）
Raiserror('删除出现错误，记录：' & @fTopName & '及其所属内容没有被删除。',16,1)
-------------------------------------------------------------
好了，这个触发器所有代码我都详细解释了，希望大家看得懂，并且通过上面的解释提高数据库操作的能力！