触发器学习笔记Word下载.docx

触发器学习笔记Word下载.docx

《触发器学习笔记Word下载.docx》由会员分享，可在线阅读，更多相关《触发器学习笔记Word下载.docx（40页珍藏版）》请在冰豆网上搜索。

例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。

更改原本要操作的SQL语句：

触发器可以修改原本要操作的SQL语句，例如原本的SQL语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。

防止数据表构结更改或数据表被删除：

为了保护已经建好的数据表，触发器可以在接收到Drop和Alter开头的SQL语句里，不进行对数据表的操作。

11.1.2 

触发器的种类

在SQLServer2005中，触发器可以分为两大类：

DML触发器和DDL触发器

DML触发器：

DML触发器是当数据库服务器中发生数据操作语言（DataManipulationLanguage）事件时执行的存储过程。

DML触发器又分为两类：

After触发器和InsteadOf触发器

DDL触发器：

DDL触发器是在响应数据定义语言（DataDefinitionLanguage）事件时执行的存储过程。

DDL触发器一般用于执行数据库中管理任务。

如审核和规范数据库操作、防止数据库表结构被修改等。

11.2 

DML触发器的分类

SQLServer2005的DML触发器分为两类：

After触发器：

这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用RollbackTransaction语句来回滚本次的操作。

InsteadOf触发器：

这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。

11.3 

DML触发器的工作原理

在SQLServer2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表，一个是删除表。

这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。

对于这两个表，用户只有读取的权限，没有修改的权限。

这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。

插入表里存放的是更新前的记录：

对于插入记录操作来说，插入表里存放的是要插入的数据；

对于更新记录操作来说，插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录：

对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；

对于删除记录操作来说，删除表里存入的是被删除的旧记录。

下面看一下触发器的工作原理。

11.3.1 

After触发器的工作原理

After触发器是在记录更变完之后才被激活执行的。

以删除记录为例：

当SQLServer接收到一个要执行删除操作的SQL语句时，SQLServer先将要删除的记录存放在删除表里，然后把数据表里的记录删除，再激活After触发器，执行After触发器里的SQL语句。

执行完毕之后，删除内存中的删除表，退出整个操作。

还是举上面的例子：

在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。

看一下数据库是怎么操作的：

（1）接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。

（2）从产品库存表里删除该产品记录。

（3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；

如果不为零的话，用RollbackTransaction语句来回滚操作。

11.3.2 

InsteadOf触发器的工作原理

InsteadOf触发器与After触发器不同。

After触发器是在Insert、Update和Delete操作完成后才激活的，而InsteadOf触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。

11.4 

设计DML触发器的注意事项及技巧

在了解触发器的种类和工作理由之后，现在可以开始动手来设计触发器了，不过在动手之前，还有一些注意事项必须先了解一下：

11.4.1 

设计触发器的限制

在触发器中，有一些SQL语句是不能使用的，这些语句包括：

表11.1 

在DML触发器中不能使用的语句

不能使用的语句

语句功能

AlterDatabase

修改数据库

CreateDatabase

新建数据库

DropDatabase

删除数据库

LoadDatabase

导入数据库

LoadLog

导入日志

Reconfigure

更新配置选项

RestoreDatabase

还原数据库

RestoreLog

还原数据库日志

另外，在对作为触发操作的目标的表或视图使用了下面的SQL语句时，不允许在DML触发器里再使用这些语句：

表11.2在目标表中使用过的，DML触发器不能再使用的语句

CreateIndex

建立索引

AlterIndex

修改索引

DropIndex

删除索引

DBCCDbreindex

重新生成索引

AlterPartitionFunction

通过拆分或合并边界值更改分区

DropTable

删除数据表

AlterTable

修改数据表结构

11.4.2 

如何在触发器取得字段修改前和修改后的数据

上面介绍过，SQLServer2005在为每个触发器都定义了两个虚拟表，一个是插入表（inserted），一个是删除表（deleted），现在把这两个表存放的数据列表说明一下：

表11.3 

插入/删除表的功能

激活触发器的动作

Inserted表

Deleted表

Insert

存放要插入的记录

Update

存放要更新的记录

存放更新前的旧记录

Delete

存放要删除的旧记录

以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零，那么判断就应该这么写：

If（Select库存数量FromDeleted）>

Begin

Print‘库存数量大于零时不能删除此记录’

RollbackTransaction

End

11.4.3 

其他注意事项

After触发器只能用于数据表中，InsteadOf触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。

一个数据表可以有多个触发器，但是一个触发器只能对应一个表。

在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器，但InsteadOf触发器针对每个操作只有建立一个。

如果针对某个操作即设置了After触发器又设置了InsteadOf触发器，那么Insteadof触发器一定会激活，而After触发器就不一定会激活了。

TruncateTable语句虽然类似于Delete语句可以删除记录，但是它不能激活Delete类型的触发器。

因为TruncateTable语句是不记入日志的。

WRITETEXT语句不能触发Insert和Update型的触发器。

不同的SQL语句，可以触发同一个触发器，如Insert和Update语句都可以激活同一个触发器。

11.5 

设计After触发器

在了解触发器及其种类、作用、工作原理之后，下面详细讲述一下要怎么去设计及建立触发器。

11.5.1 

设计简单的After触发器

下面用实例设计一个简单的AfterInsert触发器，这个触发器的作用是：

在插入一条记录的时候，发出“又添加了一种产品”的友好提示。

（1）启动ManagementStudio，登录到指定的服务器上。

（2）在如图11.1所示界面的【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库à

【表】à

【dbo.产品】，并找到【触发器】项。

图11.1定位到触发器

（3）右击【触发器】，在弹出的快捷菜单中选择【新建触发器】选项，此时会自动弹出【查询编辑器】对话框，在【查询编辑器】的编辑区里SQLServer已经预写入了一些建立触发器相关的SQL语句，如图11.2所示。

图11.2SQLServer2005预写的触发器代码

（4）修改【查询编辑器】里的代码，将从“CREATE”开始到“GO”结束的代码改为以下代码：

CREATETRIGGER产品_Insert

ON 

产品

AFTERINSERT

AS

BEGIN

print'

又添加了一种产品'

END

GO

如果有兴趣的话，也可以去修改一下如图11.2中绿色部分的版权信息。

（5）单击工具栏中的【分析】按钮

，检查一下是否语法有错，如图11.3所示，如果在下面的【结果】对话框中出现“命令已成功完成”，则表示语法没有错误。

图11.3检查语法

（6）语法检查无误后，单击【执行】按钮，生成触发器。

（7）关掉查询编辑器对话框，刷新一下触发器对话框，可以看到刚才建立的【产品_Insert】触发器，如图11.4所示。

图11.4建好的触发器

建立AfterUpdate触发器、AfterDelete触发器和建立AfterInsert触发器的步骤一致，不同的地方是把上面的SQL语句中的AFTERINSERT分别改为AFTERUPDATE和AFTERDELETE即可，如下所示，有兴趣的读者可以自行测试。

CREATETRIGGER产品_Update

AFTERUPDATE

有一种产品更改了'

CREATETRIGGER产品_Delete

AFTERDELETE

又删除了一种产品'

11.5.2 

测试触发器功能

建好AfterInsert触发器之后，现在来测试一下触发器是怎么样被激活的。

（1）在ManagementStudio里新建一个查询，在弹出的【查询编辑器】对话框里输入以下代码：

INSERTINTO产品（产品名称）VALUES（'

大苹果'

）

（2）单击【执行】按钮，可以看到【消息】对话框里显示出一句提示：

“又添加了一种产品”，如图11.5所示，这说明，AfterInsert触发器被激活，并运行成功了。

图11.5查看触发器的运行结果

而如果在【查询编辑器】里执行的不是一个Insert语句，而是一个Delete语句的话，AfterInsert触发器将不会被激活。

如在【查询编辑器】输入以下语句：

DELETEFROM产品WHERE（产品名称='

单击【执行】按钮，在【消息】对话框里只显示了一句“（1行受影响）”的提示，而没有“又添加了一种产品”的提示，如图11.6所示。

这是因为Delete语句是不能激活AfterInsert触发器，所以AfterInsert触发器里的“print‘又添加了一种产品’”语句并没有执行。

图11.6执行删除语句不会激活AfterInsert触发器

11.5.3 

建立触发器的SQL语句

回顾一下，在ManagementStudio新建一个触发器的时候，它在查询分析对话框给预设了一些SQL代码，这些代码其实上就是建立触发器的语法提示。

现在来看一下完整的触发器语法代码：

CREATETRIGGER<

Schema_Name,sysname,Schema_Name>

.<

Trigger_Name,sysname,Trigger_Name>

<

Table_Name,sysname,Table_Name>

AFTER<

Data_Modification_Statements,,INSERT,DELETE,UPDATE>

--SETNOCOUNTONaddedtopreventextraresultsetsfrom

--interferingwithSELECTstatements.

SETNOCOUNTON;

--Insertstatementsfortriggerhere

用中文改了一下，以上代码就一目了然了：

CREATETRIGGER触发器名

数据表名或视图名

AFTERINSERT或DELETE或UPDATE

--这里是要运行的SQL语句

现在再对上面的代码进行进一步的说明：

CREATETRIGGER触发器名：

这一句声明SQL语句是用来建立一个触发器。

其中触发器名在所在的数据库里必须是唯一的。

由于触发器是建立中数据表或视图中的，所以有很多人都以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名（Server.Database.Owner.TriggerName）是必须唯一的，这与触发器在哪个数据表或视图无关。

ON数据表名或视图名：

这是指定触发器所在的数据表或视图，但是请注意，只有InsteadOf触发器才能建立在视图上。

并且，有设置为WithCheckOption的视图也不允许建立InsteadOf触发器。

AFTERINSERT或DELETEUPDATE：

这是指定触发器的类型，是AfterInsert触发器，还是AfterDelete触发器，或者是AfterUpdate触发器。

其中After可以用For来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。

INSERT、DELETE和UPDATE至少要指定一个，当然也可以指定多个，若指定多个时，必须用逗号来分开。

其顺序可以任意摆放。

WithEncryption：

WithEncryption是用来加密触发器的，放在“On数据表名或视图名”的后面，“For”的前面。

如果使用了这句话，该触发器将会被加密，任何人都看不到触发器的内容了。

例一：

以下是一个包含提醒电子邮件的触发器例子，如果订单表里记录有改动的的话（无论增加订单还是修改、删除订单），则给物流人员张三发送电子邮件：

CREATETRIGGER订单_Insert

ON订单

AFTERINSERT,UPDATE,DELETE

AS

EXECmaster..xp_sendmail'

张三'

'

订单有更改，请查询确定'

例二：

在订单明细表里，折扣字段不能大于0.6，如果插入记录时，折扣大于0.6的话，回滚操作。

CREATETRIGGER订单明细_Insert

订单明细

if（Select折扣frominserted）>

0.6

begin

折扣不能大于0.6'

RollbackTransaction

end

在示例二中运用了两个方法，一个是前面说过的，在Inserted表里查询某个字段，还有一个是用RollbackTransaction来回滚操作。

如果用下面的SQL语句来进行Insert操作的话，插入记录将会不成功。

INSERTINTO订单明细（订单ID,产品ID,单价,数量,折扣） 

VALUES（11077,1,18,1,0.7）

运行结果如图11.7所示：

图11.7插入记录不符合触发器里的约束，则回滚操作

11.6 

设置After触发器的激活顺序

对于同一个操作，如Insert、Update或Delete来说，可以建立多个AfterInsert触发器，在11.5.1节中，已经建立了一个名为“产品_Insert”的触发器，现在再建立一个AfterInsert触发器，作用也是输出一句有好提示，提示内容为：

“再一次告诉你，你又添加了一种产品”。

CREATETRIGGER产品_Insert1

再一次告诉你，你又添加了一种产品'

重新运行一下插入产品的SQL语句：

INSERTINTO产品（产品名称）

VALUES（'

如图11.8所示，运行一个Insert语句，在【消息】可以看到一共输出了两句话，说明激活两个不同的触发器。

图11.8一个语句激活两个触发器

当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会变得越来越重要了。

在SQLServer2005里，用存储过程【sp_settriggerorder】可以为每一个操作各指定一个最先执行的After触发器和最后执行的After触发器。

sp_settriggerorder语法如下：

sp_settriggerorder[@triggername=]'

[triggerschema.]triggername'

[@order=]'

value'

[@stmttype=]'

statement_type'

[,[@namespace=]{'

DATABASE'

|'

SERVER'

|NULL}]

翻译成中文就是

sp_settriggerorder触发器名,

激活次序,

激活触发器的动作

解释如下：

触发器名，要用单引号括起来，因为它是一个字符串。

激活次序可以为First、Last和None：

First是指第一个要激活的触发器；

Last是指它最后一个要激活的触发器；

None是不指激活序，由程序任意触发。

激活触发器的动作可以是：

Insert、Update和Delete。

上面的例子里，先激活的是【产品_Insert】触发器，后激活的是【产品_Insert1】触发器。

如果把【产品_Insert1】触发器设为First触发器，把【产品_Insert】触发器设为Last触发器，那么结果将会完全不一样。

设置语句如下：

Execsp_settriggerorder

产品_Insert1'

'

First'

Insert'

go

产品_Insert'

’Last’,'

Go

运行结果如图11.9，与图11.8比较一下，是不是激活次序已经发生变化了？

图11.9按次序激活的激活器

在设置After触发器激活顺序时，还有几点是需要注意的：

每个操作最多只能设一个First触发器和一个Last触发器。

如果要取消已经设好的First触发器或Last触发器，只要把它们设为None触发器即可。

如果用Alter命令修改过触发器内容后，该触发器会自动变成None触发器。

所以用Alter命令也可以用来取消已经设好的First触发器或Last触发器。

只有After触发器可以设置激活次序，InsteadOf触发器不可以设置激活次序。

激活触发器的动作必须和触发器内部的激活动作一致。

举例说明：

AfterInsert触发器，只能为Insert操作设置激活次序，不能为Delete操作设置激活次序。

以下的设置是错误的：

’Update’

11.7 

触发器的嵌套

当一个触发器执行时，能够触活另一个触发器，这种情况就是触发器的嵌套。

在SQLServer2005里，触发器能够嵌套到32层。

如果不想对触发器进行嵌套的话，可以通过【允许触发器激活其他触发器】的服务器配置选项来控制。

但不管此设置是什么，都可以嵌套InsteadOf触发器。

设置触发器嵌套的选项更改方法为：

（1）打开ManagementStudio，在【对象资源管理】中，右击服务器名，并选择【属性】选项。

（2）单击【高级】节点。

（3）在【杂项】里设置【允许触发器激活