sql server 2005学习笔记之触发器简介(一)

转载 2011年05月14日 18:21:00

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。

11.1  触发器简介

触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T-SQL语句时自动执行的一种存储过程。在SQL Server 2005中,根据SQL语句的不同,把触发器分为两类:一类是DML触发器,一类是DLL触发器。

11.1.1  触发器的概念和作用

SQL Server 2005里,可以用两种方法来保证数据的有效性和完整性:约束(check)和触发器(Trigger)。约束是直接设置于数据表内,只能现实一些比较简 单的功能操作,如:实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复(即主键)、确保数据表对应的完整性(即外键)等功能。

触发器是针对数据表(库)的特殊的存储过程,当这个表发生了 Insert、Update或Delete操作时,会自动激活执行的,可以处理各种复杂的操作。在SQL Server 2005中,触发器有了更进一步的功能,在数据表(库)发生Create、Alter和Drop操作时,也会自动激活执行。

触发器常用的一些功能如下:

l  完成比约束更复杂的数据约束:触发器可以实现比约束更为复杂的数据约束

l  检查所做的SQL是否允许:触发器可以检查SQL所做的操作是否被允许。例如:在产品库存表里,如果要删除一条产品记录,在删除记录时,触发器可以检查该产品库存数量是否为零,如果不为零则取消该删除操作。

l  修改其它数据表里的数据:当一个SQL语句对数据表进行操作的时候,触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如:一个订单取消的时候,那么触发器可以自动修改产品库存表,在订购量的字段上减去被取消订单的订购数量。

l  调用更多的存储过程:约束的本身是不能调用存储过程的,但是触发器本身就是一种存储过程,而存储过程是可以嵌套使用的,所以触发器也可以调用一个或多过存储过程。

l  发送SQL Mail:在SQL语句执行完之后,触发器可以判断更改过的记录是否达到一定条件,如果达到这个条件的话,触发器可以自动调用SQL Mail来发送邮件。例如:当一个订单交费之后,可以物流人员发送Email,通知他尽快发货。

l  返回自定义的错误信息:约束是不能返回信息的,而触发器可以。例如插入一条重复记录时,可以返回一个具体的友好的错误信息给前台应用程序。

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

l  防止数据表构结更改或数据表被删除:为了保护已经建好的数据表,触发器可以在接收到Drop和Alter开头的SQL语句里,不进行对数据表的操作。

11.1.2  触发器的种类

SQL Server 2005中,触发器可以分为两大类:DML触发器和DDL触发器

l  DML触发器:DML触发器是当数据库服务器中发生数据操作语言(Data Manipulation Language)事件时执行的存储过程。DML触发器又分为两类:After触发器和Instead Of触发器

l  DDL触发器:DDL触发器是在响应数据定义语言(Data Definition Language)事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。

11.2  DML触发器的分类

SQL Server 2005的DML触发器分为两类:

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

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

11.3  DML触发器的工作原理

SQL Server 2005里,为每个DML触发器都定义了两个特殊的表,一个是插入表,一个是删除表。这两个表是建在数据库服务器的内存中的,是由系统管理的逻辑表,而不是真正存储在数据库中的物理表。对于这两个表,用户只有读取的权限,没有修改的权限。

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

插入表里存放的是更新前的记录:对于插入记录操作来说,插入表里存放的是要插入的数据;对于更新记录操作来说,插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录:对于更新记录操作来说,删除表里存放的是更新前的记录(更新完后即被删除);对于删除记录操作来说,删除表里存入的是被删除的旧记录。

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

11.3.1  After触发器的工作原理

After触发器是在记录更变完之后才被激活执行的。以删除记录为 例:当SQL Server接收到一个要执行删除操作的SQL语句时,SQL Server先将要删除的记录存放在删除表里,然后把数据表里的记录删除,再激活After触发器,执行After触发器里的SQL语句。执行完毕之后, 删除内存中的删除表,退出整个操作。

还是举上面的例子:在产品库存表里,如果要删除一条产品记录,在删除记录时,触发器可以检查该产品库存数量是否为零,如果不为零则取消删除操作。看一下数据库是怎么操作的:

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

(2)从产品库存表里删除该产品记录。

(3)从删除表里读出该产品的库存数量字段,判断是不是为零,如果为零的话,完成操作,从内存里清除删除表;如果不为零的话,用Rollback Transaction语句来回滚操作。

11.3.2  Instead Of触发器的工作原理

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

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

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

11.4.1  设计触发器的限制

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

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

不能使用的语句

语句功能

Alter Database

修改数据库

Create Database

新建数据库

Drop Database

删除数据库

Load Database

导入数据库

Load Log

导入日志

Reconfigure

更新配置选项

Restore Database

还原数据库

Restore Log

还原数据库日志

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

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

不能使用的语句

语句功能

Create Index

建立索引

Alter Index

修改索引

Drop Index

删除索引

DBCC Dbreindex

重新生成索引

Alter Partition Function

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

Drop Table

删除数据表

Alter Table

修改数据表结构

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

上面介绍过,SQL Server 2005在为每个触发器都定义了两个虚拟表,一个是插入表(inserted),一个是删除表(deleted),现在把这两个表存放的数据列表说明一下:

表11.3  插入/删除表的功能

激活触发器的动作

Inserted表

Deleted表

Insert

存放要插入的记录


Update

存放要更新的记录

存放更新前的旧记录

Delete


存放要删除的旧记录

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

If (Select 库存数量 From Deleted)>0

Begin

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

Rollback Transaction

End

11.4.3  其他注意事项

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

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

l  在同一个数据表中,对每个操作(如Insert、Update、Delete)而言可以建立许多个After触发器,但Instead Of触发器针对每个操作只有建立一个。

l  如果针对某个操作即设置了After触发器又设置了Instead Of触发器,那么Instead of触发器一定会激活,而After触发器就不一定会激活了。

l  Truncate Table语句虽然类似于Delete语句可以删除记录,但是它不能激活Delete类型的触发器。因为Truncate Table语句是不记入日志的。

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

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

11.5  设计After触发器

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

11.5.1  设计简单的After触发器

下面用实例设计一个简单的After Insert触发器,这个触发器的作用是:在插入一条记录的时候,发出“又添加了一种产品”的友好提示。

(1)启动Management Studio,登录到指定的服务器上。

(2)在如图11.1所示界面的【对象资源管理器】下选择【数据库】,定位到【Northwind】数据库à【表】à【dbo.产品】,并找到【触发器】项。

图11.1 定位到触发器

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

图11.2 SQL Server 2005预写的触发器代码

(4)修改【查询编辑器】里的代码,将从“CREATE”开始到“GO”结束的代码改为以下代码:

CREATE TRIGGER 产品_Insert

   ON  产品

   AFTER INSERT

AS

BEGIN

         print '又添加了一种产品'

END

GO

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

(5)单击工具栏中的【分析】按钮 ,检查一下是否语法有错,如图11.3所示,如果在下面的【结果】对话框中出现“命令已成功完成”,则表示语法没有错误。

图11.3 检查语法

(6)语法检查无误后,单击【执行】按钮,生成触发器。

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

图11.4 建好的触发器

建立After Update触发器、After Delete触发器和建立After Insert触发器的步骤一致,不同的地方是把上面的SQL语句中的AFTER INSERT分别改为AFTER UPDATE和AFTER DELETE即可,如下所示,有兴趣的读者可以自行测试。

CREATE TRIGGER 产品_Update

   ON  产品

   AFTER UPDATE

AS

BEGIN

         print '有一种产品更改了'

END

GO

CREATE TRIGGER 产品_Delete

   ON  产品

   AFTER DELETE

AS

BEGIN

         print '又删除了一种产品'

END

GO

11.5.2  测试触发器功能

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

(1)在Management Studio里新建一个查询,在弹出的【查询编辑器】对话框里输入以下代码:

INSERT INTO 产品(产品名称) VALUES ('大苹果')

(2)单击【执行】按钮,可以看到【消息】对话框里显示出一句提示:“又添加了一种产品”,如图11.5所示,这说明,After Insert触发器被激活,并运行成功了。

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

而如果在【查询编辑器】里执行的不是一个Insert语句,而是一个Delete语句的话,After Insert触发器将不会被激活。如在【查询编辑器】输入以下语句:

DELETE FROM 产品 WHERE (产品名称= '大苹果')

单击【执行】按钮,在【消息】对话框里只显示了一句“(1行受影 响)”的提示,而没有“又添加了一种产品”的提示,如图11.6所示。这是因为Delete语句是不能激活After Insert触发器,所以After Insert触发器里的“print '又添加了一种产品’”语句并没有执行。

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

11.5.3  建立触发器的SQL语句

回顾一下,在Management Studio新建一个触发器的时候,它在查询分析对话框给预设了一些SQL代码,这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码:

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>

   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>

AS

BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from

         -- interfering with SELECT statements.

         SET NOCOUNT ON;

    -- Insert statements for trigger here

END

GO

用中文改了一下,以上代码就一目了然了:

CREATE TRIGGER 触发器名

   ON  数据表名或视图名

   AFTER INSERT或DELETE或UPDATE

AS

BEGIN

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

END

GO

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

l  CREATE TRIGGER 触发器名:这一句声明SQL语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的,所以有很多人都 以为只要是在不同的数据表中,触发器的名称就可以相同,其实触发器的全名(Server.Database.Owner.TriggerName)是必须 唯一的,这与触发器在哪个数据表或视图无关。

l  ON 数据表名或视图名:这是指定触发器所在的数据表或视图,但是请注意,只有Instead Of触发器才能建立在视图上。并且,有设置为With Check Option的视图也不允许建立Instead Of触发器。

l  AFTER INSERT或 DELETE UPDATE:这是指定触发器的类型,是After Insert触发器,还是After Delete触发器,或者是After Update触发器。其中After可以用For来代取,它们的意思都是一样的,代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERT、 DELETE和UPDATE至少要指定一个,当然也可以指定多个,若指定多个时,必须用逗号来分开。其顺序可以任意摆放。

l  With Encryption:With Encryption是用来加密触发器的,放在“On 数据表名或视图名”的后面,“For”的前面。如果使用了这句话,该触发器将会被加密,任何人都看不到触发器的内容了。

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

CREATE TRIGGER 订单_Insert

ON 订单

AFTER INSERT, UPDATE, DELETE

AS

   EXEC master..xp_sendmail '张三',

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

GO

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

CREATE TRIGGER 订单明细_Insert

   ON  订单明细

   AFTER INSERT

AS

BEGIN

         if (Select 折扣 from inserted)>0.6

         begin

                   print '折扣不能大于0.6'

                   Rollback Transaction

         end

END

GO

在示例二中运用了两个方法,一个是前面说过的,在Inserted表里查询某个字段,还有一个是用Rollback Transaction来回滚操作。如果用下面的SQL语句来进行Insert操作的话,插入记录将会不成功。

INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣)  

VALUES (11077,1,18,1,0.7)

运行结果如图11.7所示:

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

11.6  设置After触发器的激活顺序

对于同一个操作,如Insert、Update或Delete来说,可以建立多个After Insert触发器,在11.5.1节中,已经建立了一个名为“产品_Insert”的触发器,现在再建立一个After Insert触发器,作用也是输出一句有好提示,提示内容为:“再一次告诉你,你又添加了一种产品”。

CREATE TRIGGER 产品_Insert1

   ON  产品

   AFTER INSERT

AS

BEGIN

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

END

GO

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

INSERT INTO 产品(产品名称)

VALUES ('大苹果')

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

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

当同一个操作定义的触发器越来越多的时候,触发器被激活的次序就会 变得越来越重要了。在SQL Server 2005里,用存储过程【sp_settriggerorder】可以为每一个操作各指定一个最先执行的After触发器和最后执行的After触发器。 sp_settriggerorder语法如下:

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'

        , [ @order = ] 'value'

        , [ @stmttype = ] 'statement_type'

        [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

翻译成中文就是

sp_settriggerorder 触发器名,

        激活次序,

        激活触发器的动作

解释如下:

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

l  激活次序可以为First、Last和None:First是指第一个要激活的触发器;Last是指它最后一个要激活的触发器;None是不指激活序,由程序任意触发。

l  激活触发器的动作可以是:Insert、Update和Delete。

上面的例子里,先激活的是【产品_Insert】触发器,后激活的是【产品_Insert1】触发器。如果把【产品_Insert1】触发器设为First触发器,把【产品_Insert】触发器设为Last触发器,那么结果将会完全不一样。设置语句如下:

Exec sp_settriggerorder

         '产品_Insert1','First','Insert'

go

Exec sp_settriggerorder

         '产品_Insert',’Last’,'Insert'

Go

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

INSERT INTO 产品(产品名称)

VALUES ('大苹果')

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

图11.9 按次序激活的激活器

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

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

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

l  如果用Alter命令修改过触发器内容后,该触发器会自动变成None触发器。所以用Alter命令也可以用来取消已经设好的First触发器或Last触发器。

l  只有After触发器可以设置激活次序,Instead Of触发器不可以设置激活次序。

l  激活触发器的动作必须和触发器内部的激活动作一致。举例说明:After Insert触发器,只能为Insert操作设置激活次序,不能为Delete操作设置激活次序。以下的设置是错误的:

Exec sp_settriggerorder

         '产品_Insert1','First',’Update’

go

相关文章推荐

SQL Server 2005 学习笔记之触发器简介[转]

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。 11.1  触发器简介 触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T-SQL...
  • wxzyq
  • wxzyq
  • 2011-08-30 19:46
  • 255

SQL Server 2005 学习笔记之触发器简介[转]

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。 11.1  触发器简介 触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T-SQL...

sql server 2005学习笔记之触发器简介

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。11.1  触发器简介触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T-SQL语句时自...

sql server 2005学习笔记之触发器简介

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。 11.1  触发器简介 触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T-S...

SQL Server 2005 学习笔记之触发器简介[转]

触发器实际上就是一种特殊类型的存储过程,其特殊性表现在:它是在执行某些特定的T-SQL语句时自动的。 11.1  触发器简介 触发器实际上就是一种特殊类型的存储过程,它是在执行某些特定的T...

SQL Server 2005 学习笔记

  • 2014-06-12 09:57
  • 1.50MB
  • 下载

黑马程序员学习笔记4---SQL SERVER 2000/2005 实现行转列和列转行

----------Windows Phone 7手机开发、.Net培训、期待与您交流! 普通行列转换 问题:假设有张学生成绩表(tb)如下: 姓名 课程 分数 张三 语文 74 张...

T-SQL 编程与应用----学习笔记一[Microsoft SQL Server 2005]

1.有效标识符 @开头的标识符表示这是一个局部变量或是一个函数的参数@@全局变量#开头的标识符表示这是一个临时表或是一个存储过程##开头的标识符表示这是一个全局的临时数据库对象2.注释(1)多行注释/...

SQL Server 2005 中实现通用的异步触发器架构收藏

在SQL Server 2005中,通过新增的Service Broker可以实现异步触发器的处理功能。本文提供一种使用Service Broker实现的通用异步触发器方法。 在本方法中,通过Ser...

SQL Server 2005 用触发器跟踪表操作

--> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int,[品名] varch...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)