触发器触发器的概念和作用

触发器的概念和作用

2010-08-3021 :1 1 :38|分类 sql2000|标签 |字号大中小订阅

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

触发器是针对数据表库的特殊的存储过程 当这个表发生了Insert、 Update或Delete操作时会自动激活执行的可以处理各种复杂的操作。在SQLServer2005中触发器有了更进一步的功能在数据表库发生C rea te、Al te r和D ro p操作时也会自动激活执行。

触发器常用的一些功能如下l完成比约束更复杂的数据约束触发器可以实现比约束更为复杂的数据约束l检查所做的SQL是否允许触发器可以检查SQL所做的操作是否被允许。例如在产品库存表里如果要删除一条产品记录在删除记录时触发器可以检查该产品库存数量是否为零如果不为零则取消该删除操作。l修改其它数据表里的数据当一个SQL语句对数据表进行操作的时候触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如一个订单取消的时候那么触发器可以自动修改产品库存表在订购量的字段上减去被取消订单的订购数量。l调用更多的存储过程约束的本身是不能调用存储过程的但是触发器本身就是一种存储过程而存储过程是可以嵌套使用的所以触发器也可以调用一个或多过存储过程。l发送SQL Mai l 在SQL语句执行完之后触发器可以判断更改过的记录是否达到一定条件如果达到这个条件的话触发器可以自动调用SQL Mai l来发送邮件。例如当一个订单交费之后可以物流人员发送Emai l 通知他尽快发货。l返回自定义的错误信息约束是不能返回信息的而触发器可以。例如插入一条重复记录时可以返回一个具体的友好的错误信息给前台应用程序。l更改原本要操作的SQL语句触发器可以修改原本要操作的SQL语句例如原本的SQL语句是要删除数据表里的记录但该数据表里的记录是最要记录不允许删除的那么触发器可以不执行该语句。l防止数据表构结更改或数据表被删除为了保护已经建好的数据表触发器可以在接收到D ro p和Alter开头的SQL语句里不进行对数据表的操作。

1 1 1 2触发器的种类

在SQLServer2005中触发器可以分为两大类 DML触发器和DDL触发器l DML触发器 DML触发器是当数据库服务器中发生数据操作语言Data Manipulation Language事件时执行的存储过程。 DML触发器又分为两类 After触发器和Instead Of触发器

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

1 1 2 DML触发器的分类

SQLServer2005的DML触发器分为两类l Afte r触发器这类触发器是在记录已经改变完之后afte r 才会被激活执行它主要是用于记录变更后的处理或检查一旦发现错误也可以用Rol lback Transaction语句来回滚本次的操作。l Instead Of触发器这类触发器一般是用来取代原本的操作在记录变更之前发生的它并不去执行原来SQL语句里的操作Insert、 Update、 Delete 而去执行触发器本身所定义的操作。

1 1 3 DML触发器的工作原理

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

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

插入表里存放的是更新前的记录对于插入记录操作来说插入表里存放的是要插入的数据对于更新记录操作来说插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录对于更新记录操作来说删除表里存放的是更新前的记录更新完后即被删除 对于删除记录操作来说删除表里存入的是被删除的旧记录。

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

1 1 31 After触发器的工作原理

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

还是举上面的例子在产品库存表里如果要删除一条产品记录在删除记录时触发器可以检查该产品库存数量是否为零如果不为零则取消删除操作。看一下数据库是怎么操作的 1 接收SQL语句将要从产品库存表里删除的产品记录取出来放在删除表里。 2从产品库存表里删除该产品记录。3从删除表里读出该产品的库存数量字段判断是不是为零如果为零的话完成操作从内存里清除删除表如果不为零的话用Rol lback Transaction语句来回滚操作。

1 1 32 Instead Of触发器的工作原理

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

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

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

1 141设计触发器的限制

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

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

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

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

上面介绍过 SQL Server2005在为每个触发器都定义了两个虚拟表一个是插入表inserted 一个是删除表deleted 现在把这两个表存放的数据列表说明一下

表1 1 3插入/删除表的功能

以上面删除库存产品记录为例在删除时触发器要判断库存数量是否为零那么判断就应该这么写f(Select库存数量From Deleted)>0

Begin

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

Rol lback Transaction

End

1 143其他注意事项l After触发器只能用于数据表中 Instead Of触发器可以用于数据表和视图上但两种触发器都不可以建立在临时表上。l一个数据表可以有多个触发器但是一个触发器只能对应一个表。l在同一个数据表中对每个操作如Insert、 U pdate、 Delete而言可以建立许多个After触发器但Instead Of触发器针对每个操作只有建立一个。l如果针对某个操作即设置了After触发器又设置了Instead Of触发器那么Instead of触发器一定会激活而Afte r触发器就不一定会激活了。l Truncate Table语句虽然类似于Delete语句可以删除记录但是它不能激活Delete类型的触发器。因为Truncate Table语句是不记入日志的。l WRITETEXT语句不能触发Insert和Update型的触发器。l不同的SQL语句可以触发同一个触发器如Insert和Update语句都可以激活同一个触发器。

1 1 5设计After触发器

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

1 1 51设计简单的Afte r触发器

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

1 启动Management Studio登录到指定的服务器上。

2在如图1 1 1所示界面的【对象资源管理器】下选择【数据库】 定位到【N o rthw i n d】数据库à 【表】 à 【d bo产品】 并找到【触发器】项。

图1 1 1定位到触发器

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

图1 1 2 SQLServer2005预写的触发器代码

4修改【查询编辑器】里的代码将从“CREATE”开始到“GO”结束的代码改为以下代码

CREATE TRIGG ER产品_Insert

ON产品

AFTER INSERT

AS

BEGINprint'又添加了一种产品'

END

GO

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

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

6语法检查无误后单击【执行】按钮生成触发器。

7关掉查询编辑器对话框刷新一下触发器对话框可以看到刚才建立的【产品_I ns e rt】触发器。

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

CREATE TRIGG ER产品_Update

ON产品

AFTER UPDATE

AS

BEGINprint'有一种产品更改了'

END

GO

CREATE TRIGG ER产品_Delete

ON产品

AFTER DELETE

AS

BEGINprint'又删除了一种产品'

END

GO

1 1 52测试触发器功能

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

1 在Management Studio里新建一个查询在弹出的【查询编辑器】对话框里输入以下代码

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

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

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

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

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

1 1 53建立触发器的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_Statem ents, , INSERT,DELETE,U PDAT E>

AS

BEGIN

--SET NOCOUNT ON added to preventextra resultsets from

-- interfering with SELECT statements

SETNOCOUNTON;

-- Insertstatementsfortrigger here

END

GO

用中文改了一下 以上代码就一目了然了

CREATETRIGGER触发器名

ON数据表名或视图名

AFTER INSERT或DELETE或UPDATE

AS

BEGIN

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

END

GO

现在再对上面的代码进行进一步的说明l CREATETRIGG ER触发器名这一句声明SQL语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的所以有很多人都以为只要是在不同的数据表中触发器的名称就可以相同其实触发器的全名Server Database OwnerTriggerName是必须唯一的这与触发器在哪个数据表或视图无关。l ON数据表名或视图名这是指定触发器所在的数据表或视图但是请注意只有Instead Of触发器才能建立在视图上。并且有设置为With CheckOption的视图也不允许建立Instead Of触发器。lAFTER INSERT或DELETE UPDATE这是指定触发器的类型是After Insert触发器还是AfterDelete触发器或者是After Update触发器。其中After可以用For来代取它们的意思都是一样的代表只有在数据表的操作都已正确完成后才会激活的触发器。 INSERT、 DELETE和UPDATE至少要指定一个 当然也可以指定多个若指定多个时必须用逗号来分开。其顺序可以任意摆放。l With EncryptionWith Encryption是用来加密触发器的放在“On数据表名或视图名”的后面 “For”的前面。如果使用了这句话该触发器将会被加密任何人都看不到触发器的内容了。

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

CREATE TRIGG ER订单_Insert

ON订单

AFTER INSERT,UPDATE,DELETE

AS

EXEC master xp_sendmail '张三',

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

GO

例二在订单明细表里折扣字段不能大于06如果插入记录时折扣大于06的话 回滚操作。CREATETRIGGER订单明细_Insert

ON订单明细

AFTER INSERT

AS

BEGINif(Select折扣from inserted)>06beginprint '折扣不能大于06'

Rol l back Transactionend

END

GO

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

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

VALUES(1 1077,1 ,18,1 ,07)

运行结果如图1 1 3所示

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

1 1 6设置After触发器的激活顺序

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

CREATE TRIGG ER产品_Insert1

ON产品

AFTER INSERT

AS

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

END

GO

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

INSERTINTO产品(产品名称)

VALUES('大苹果')

如图1 14所示运行一个I n s e rt语句在【消息】可以看到一共输出了两句话说明激活两个不同的触发器。 图1 14一个语句激活两个触发器

当同一个操作定义的触发器越来越多的时候触发器被激活的次序就会变得越来越重要了。在SQLServer 2005里用存储过程【s p_s ettriggerord er】可以为每一个操作各指定一个最先执行的After触发器和最后执行的Aft e r触发器。 s p_s ettri g g e ro rd e r语法如下s p_s ettriggerorder [@triggername=] '[triggers chem a ] triggername'

, [@o rd e r=] 'va l u e'

, [@stmttype=] 'statement_type'

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

翻译成中文就是