sqlserver 触发器 mysql_sqlserver 触发器教程

sqlserver 触发器教程

作者：匿名 字体：[增加 减小] 来源：互联网 时间：2018-12-05

匿名通过本文主要向大家介绍了sqlserver,触发器等相关知识,希望本文的分享对您有所帮助

级联删除，如果要删除主类别表的记录，那么把次类别表所属记录以及这个类别所包含的所有文章内容删除

主类别表名：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，表示欲删除的主类别记录包含有子类别，不能直接删除，必须先删除子类别。

但删除子类别必须删除子类别下包含的所有内容(文章)记录，所以我们必须判断是否有内容记录

------------------------------------------