触发器

     触发器是一种特殊的存储过程，类似于事件函数，SQL Server™ 允许为 INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL语句。

 一：SQL Server 2000触发器

   触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。

　　创建触发器用 CREATE TRIGGER

CREATE TRIGGER 触发器名称
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS

 T-SQL 语句

　　注意：触发器名称是不加引号的。

　　如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
　 EXEC master..xp_sendmail 'MaryM',
　　　'Don''t forget to print a report for the distributors.'

 

　　重命名触发器

　　用查询分析器重命名：

　　exec sp_rename 原名称, 新名称

　　sp_rename 是 SQL Server™ 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。

　　用企业管理器重命名：

　　在表上点右键->“所有任务”->“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。

　　删除触发器

　　用查询分析器删除：

　　在查询分析器中使用 drop trigger 触发器名称 来删除触发器。 

      也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称...

　　注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：

　　if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')

　　用企业管理器删除：

　　在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。

　　如何查看某个触发器的内容

　　用查询分析器查看:

use 数据库名
go
exec sp_helptext '触发器名称'

 

　　将会以表的样式显示触发器内容。

　　除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

　　用企业管理器查看：

　　在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器。

　　如何查看当前数据库中有哪些触发器

　　在查询分析器中运行：

use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects
保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。
在 name 一列，我们可以看到触发器名称。
sp_helptrigger

 

　　存储过程 sp_helptrigger 用于查看触发器的属性。

　　sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

　　例：

use 数据库名
go
exec sp_helptrigger tbl

 触发器更多语法

 INSTEAD OF:

　　执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例：

create trigger f
on tbl
instead of delete
as
insert into Logs...

 　　IF UPDATE(列名):

　　检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：

create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列

 

　　inserted、deleted:

　　这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：

create trigger tbl_delete
on tbl
for delete
as
　　declare @title varchar(200)
　　select @title=title from deleted
　　insert into Logs(logContent)
values('删除了 title 为：' + title + '的记录')

 　说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。

　　触发器回滚

　　举例:

　　我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。

use 数据库名
go
create trigger tr
on 表名
for update
as
　　if update(userName)
　　　　rollback tran  

　　关键在最后两句，其解释为：如果更新了 userName 列，就回滚事务。 

　　递归、嵌套触发器

　　递归触发器:

　　递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。

　　间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1...

　　直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1...

　　嵌套触发器：

　　类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。

　　设置直接递归:

　　默认情况下是禁止直接递归的，要设置为允许有两种方法：

　　T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true EM：数据库上点右键->属性->选项。 设置间接递归、嵌套

　　默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：

　　T-SQL：exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许 EM：注册上点右键->属性->服务器设置。

 

二 sql server 2005触发器

在SQL
      Server 2005中，根据SQL语句的不同，把触发器分为两类：一类是DML触发器，一类是DLL触发器。 

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

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

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

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

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

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

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

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

    自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。更改原本要操作的SQL语句：触发器可以修改原本要操作的SQL语句，例如原本的SQL语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。

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

   在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发

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

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

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

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

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

   DML触发器的工作原理

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

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

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

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

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

After触发器的工作原理

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

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

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

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

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

Instead Of触发器的工作原理

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

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

设计触发器的限制

在触发器中，有一些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	修改数据表结构

 

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

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

表11.3  插入/删除表的功能

激活触发器的动作	Inserted表	Deleted表
Insert	存放要插入的记录
Update	存放要更新的记录	存放更新前的旧记录
Delete		存放要删除的旧记录

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

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

Begin

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

Rollback Transaction

End

其他注意事项

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语句都可以激活同一个触发器。

设计简单的After触发器

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

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

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

 

SQL Server 2005预写的触发器代码

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

CREATE TRIGGER 产品_Insert

   ON  产品

   AFTER INSERT

AS

BEGIN

         print '又添加了一种产品'

END

GO

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

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

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

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

建立After Update触发器、After Delete触发器和建立After Insert触发器的步骤一致，不同的地方是把上面的SQL语句中的AFTER INSERT分别改为AFTER UPDATE和AFTER DELETE即可

建立触发器的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)

设置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 ('大苹果')

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

当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会 变得越来越重要了。在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 ('大苹果')

在设置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里，触发器能够嵌套到32层。

如果不想对触发器进行嵌套的话，可以通过【允许触发器激活其他触发器】的服务器配置选项来控制。但不管此设置是什么，都可以嵌套Instead Of触发器。设置触发器嵌套的选项更改方法为：

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

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

（3）在【杂项】里设置【允许触发器激活其他触发器】为True或False。

 

sql server 2005的转于

http://www.360doc.com/content/09/0527/16/144699_3675107.shtml