第11章 触发器.docx
《第11章 触发器.docx》由会员分享,可在线阅读,更多相关《第11章 触发器.docx(18页珍藏版)》请在冰豆网上搜索。
![第11章 触发器.docx](https://file1.bdocx.com/fileroot1/2022-12/13/fb2e37be-77d9-46a9-89f7-0f440e126b6f/fb2e37be-77d9-46a9-89f7-0f440e126b6f1.gif)
第11章触发器
第11章触发器
ÿ本章学习目标
严格地说,触发器也是一种特殊类型的存储过程,它与表的关系很密切,常用于保护表中的数据。
本章主要内容包括触发器的概念、作用、类型,创建触发器的方法,如何对触发器进行管理等。
通过本章的学习,读者应了解触发器的概念、作用、类型,熟悉利用存储过程和触发器维护数据完整性的方法,掌握创建和管理触发器的方法等。
ÿ学习重点与难点
☑触发器的概念、作用、类型等
☑创建触发器的方法
☑查看、修改和删除触发器的方法
☑查看依赖关系
◆利用存储过程和触发器维护数据完整性的方法
11.1触发器概述
学习和使用触发器,首先应对概念等有个详细的了解。
本节就先来介绍有关触发器的概念、作用、类型等内容。
11.1.1触发器的概念
触发器(triegger)是一种特殊类型的存储过程,它与表紧密相连,可看作是表格定义的一部分。
触发器是在特定表上进行定义的,该表也称为触发器表。
触发器不能被显式地调用,当有操作针对触发器表时,例如在表中插入、删除、修改数据时,如果该表有相应操作类型的触发器,那么触发器就自动触发执行。
使用触发器可实施更为复杂的数据完整性约束。
触发器基于一个表创建,但是可以针对多个表进行操作,所以触发器常被用来实现复杂的商业规则。
例如,在pubs数据库中,存放着出版商(publishers)的信息,存放着出版物(titles)的信息,还存放着出版物与作者关联的信息(titleauthor)以及作者信息(authors)。
现在,有一条出版商的信息被删除了,则所有由该出版商出版的出版物都应该将pu_id修改为NULL,或者删除有关的出版物信息。
同样,titleauthor表中的信息也应该相应地得到修改。
这种涉及到三张表的一致性维护问题,可以使用触发器来实现。
在publishers表上设置一个DELETE触发器,当删除一条publishers信息时,触发器自动执行,对titles表和publishers进行修改。
在SQLServer中,一张表可以有多个触发器。
用户可以针对INSERT、UPDATE或DELETE语句分别设置触发器,也可以针对一张表上的特定操作设置多个触发器。
触发器里可以容纳非常复杂的T-SQL语句。
但是,不管触发器所进行的操作有多复杂,触发器都只作为一个独立的单元被执行,被看作是一个事务。
如果在执行触发器的过程中发生错误,则整个事务将会被自动回滚。
触发器和存储过程也是有区别的:
存储过程是在数据库上定义的,而触发器是在特定表上进行定义的;存储过程可以由用户直接调用执行,但触发器不能直接调用执行,而是SQLSERVER自动触发执行的。
触发器不允许带参数。
11.1.2触发器的作用
使用触发器的最终目的是更好地维护企业的业务规则。
在实际应用中,触发器主要提供以下功能:
◆级联修改数据库中的所有相关表,如上一节中所述。
◆撤消回滚违反引用完整性的操作,防止非法修改数据。
◆执行比检查约束更复杂的约束操作。
◆查找在数据库修改前后,表状态之间的差别,并根据差别来分别采取相应的措施。
◆在一张表的同一类型的操作是设置多个触发器,从而可以针对同样的修改语句执行不同的多种操作。
11.1.3触发器的类型
SQLServer2000提供了两种触发器:
INSTEADOF和AFTER触发器。
这两种触发器的差别在于他们被激活的时机不同:
◆AFTER触发器在触发它们的语句完成后执行。
AFTER触发器在约束检查之后执行,如果该语句因错误(如违反约束或语法错误)而失败,触发器将不会执行。
不能为视图指定AFTER触发器,只能为表指定该触发器。
可以为每个触发操作(INSERT、UPDATE或DELETE)指定多个AFTER触发器。
如果表有多个AFTER触发器,可使用sp_settriggerorder定义哪个AFTER触发器最先激发,哪个最后激发。
除第一个和最后一个触发器外,所有其它的AFTER触发器的激发顺序不确定,并且无法控制。
在SQLServer2000中AFTER是默认触发器。
不能在SQLServer7.0版或更早的版本中指定AFTER或INSTEADOF,这些版本中的所有触发器都作为AFTER触发器运行。
◆INSTEADOF触发器是SQLServer2000引进的一种新的触发器类型,用于替代引起触发器执行的T-SQL语句。
可在表和视图上指定INSTEADOF触发器。
在SQLServer2000中,不能为每个触发操作(INSERT、UPDATE和DELETE)定义多个INSTEADOF触发器。
INSTEADOF触发器在约束检查之前执行。
11.1.4与触发器密切相关的两个专用表
在使用触发器过程中,SQLServer使用到了两个特殊的临时表:
inserted表和deleted表。
这是两个逻辑表,由系统来维护,不允许用户直接对这两个表进行修改。
这两张表都存在与高速缓存中(如果内存不够用,也可能存储在硬盘上),实际上是事务日志的视图,它们与被该触发器作用的表的结构相同。
触发器工作完成后,这两个表也会被删除。
用户可以使用这两张临时表来检测某些修改操作所产生的效果。
例如,可以使用SELECT语句来检查INSERT语句和UPDATE语句执行的插入操作是否成功,触发器被这些语句触发等。
但是不允许用户直接修改inserted和deleted临时表中的数据。
inserted和deleted表都是针对当前触发器的局部临时表,这些表只对应于当前触发器的基本表。
如果在触发器中使用、了存储过程,或者是产生了嵌套触发器的情况,则不同的触发器将会使用属于自己基本表的inserted和deleted临时表。
◆inserted表:
存储着被DELETE和UPDATE语句影响的新的数据行。
当用户执行DELETE或UPDATE语句时,新的数据行被添加到inserted表中,同时这些数据行的备份被复制到inserted临时表中。
◆deleted表:
存储着被DELETE和UPDATE语句影响的旧数据行。
在执行DELETE或UPDATE语句过程中,指定的数据行被用户从基表中删除,然后转移到了deleted表中。
一般来说,在基表和deleted表中不会存在有相同的数据行。
对INSERT操作,只在inserted表中保存所插入的新行,而deleted表中无数据。
对于DELETE操作,只在deleted表中保存被删除的旧行,而inserted表中无数据。
对于UPDATE操作,可以将它看作先执行一个DELETE操作,再执行一个INSERT操作的结果,旧的行首先被移动到deleted表中,然后新行同时插入激活触发器的表和inserted表中,所以在inserted表中存放着更新后的新行值,deleted表中存放着更新前的旧行值。
11.2创建触发器
在创建触发器之前,用户需要注意以下的几点事项:
◆CRAETETRIGGER语句必须是一个批中的第一条语句。
◆创建触发器的权限默认是属于表的所有者的,而且不能再授权给他人。
◆触发器是数据库对象,它的命名必须符合命名规则。
◆只能在当前数据库中创建触发器,但触发器可以引用其他数据库的对象。
◆触发器不能在临时表或系统表上创建,触发器中可引用临时表,但不能引用系统表。
◆尽管TRUNCATETABLE语句很像一个没有WHERE子句的DELETE语句(二者都是删除表中的所有行),但TRUNCATETABLE的操作不被记入事务日志,所以它也不会激活DELETE触发器。
◆WRITETEXT语句不会激活INSERT或UPDATE触发器。
◆如果指定触发器所有者名限定触发器,要以相同的方式限定表名。
◆在同一个CRAETETRIGGER语句中,可以为多种操作定义相同的触发器操作。
◆如果一个表的外键在DELETE、UPDATE操作上定义了级联,则不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。
◆在触发器内可以指定任意的SET语句,所选择的SET选项在触发器执行期间有效,并在触发器执行完后恢复到以前的设置。
◆触发器不能返回任何结果,为了阻止从触发器返回结果,不在触发器定义中包含SELECT语句或变量赋值。
如果必须在触发器中进行变量赋值,则应该在触发器的开头使用SETNOCOUNT语句,以免返回任何结果集。
在创建触发器时需要制定以下内容:
◆触发器的名称。
◆触发器所基于的表或视图。
◆触发器激活的时机。
◆激活触发器的修改语句,有效的选项是INSERT、UPDATE和DELETE。
◆触发器执行的语句。
11.2.1使用T-SQL语句创建触发器
使用T-SQL语句创建触发器的语法格式为:
CREATETRIGGERtrigger_name
ON[table_name|view_name]
[WITHENCRYPTION]
{FOR|AFTER|INSTEADOF}
{[DELETE][,][INSERT][,][UPDATE]}
[NOTFORREPLICATION]
AS
sql_statement
其中各选项的含义如下:
◆trigger_name:
触发器名称。
◆table_name、view_name:
指出了所创建的触发器与之相关联的表或视图的名字。
◆WITHENCRYPTION:
触发器作为一种数据库对象,在syscomments表中存储有完整的文本定义信息。
可以使用WITHENCRYPTION对访问syscomments表不入口进行加密。
◆FOR|AFTER|INSTEADOF:
指定触发器的类型,AFTER为默认类型。
◆NOTFORREPLICATION:
定义在复制过程中,不执行触发器操作。
11.2.2创建AFTER触发器
1、INSERT触发操作
INSERT(插入)触发操作触发器的执行过程如下:
(1)首先执行INSERT语句进行数据插入。
系统检查被插入新值的正确性(如:
约束等),如果正确,将新行插入到表中。
(2)执行该表INSERT触发器中的相应语句。
如果执行到ROLLBACK操作,则系统将回滚整个操作(删除第一步插入的新值,对触发器中已经执行的操作做反操作)。
例如,为Supermarket数据库的Goods表创建一个INSERT触发器,当在Goods表中插入一行数据时,若该供应商号在Supplier表中不存在,则给出出错信息,并使插入不能进行(只针对插入单行数据,不包含对多行数据的判断)。
创建触发器的具体代码如下:
USESupermarket
/*如果存在同名的触发器,则删除之*/
IFEXISTS(SELECTnameFROMsysobjects
WHEREtype='TR'ANDname='goods_insert')
DROPTRIGGERgoods_insert
GO
/*创建触发器*/
CREATETRIGGERgoods_insert
ONGoods
AFTERINSERT
AS
IFNOTEXISTS(SELECT*FROMGoodsJOINSupplier
ONGoods.供应商号=Supplier.供应商号)
BEGIN
Print'插入的供应商号在Supplier表中不存在,插入无效!
'
Rollback
END
以下语句用于向Goods表中插入数据:
USESupermarket
INSERTINTOGoods
VALUES('2','小金人纸巾','120',11,'20卷装','2',14)
因为Supplier表中无供应商号为2的记录,所以返回:
“插入的供应商号在Supplier表中不存在,插入无效!
”
触发器与触发它的语句作为同一事物的一部分来执行。
即触发器与触发它的语句处于同一事物之中,要么都执行,要么都不执行。
2、DELETE触发操作
DELETE(删除)触发操作触发器的执行过程如下:
(1)先执行DELETE语句进行数据删除。
系统检查被删除的正确性(如:
约束等),如果正确,将数据从表中删除。
(2)执行该表DELETE触发器中的相应语句。
如果执行到ROLLBACK操作,则系统将回滚整个操作(恢复第一步删除的值,对触发器中已经执行的操作做反操作)。
例:
为Sales数据库的Customers表创建一个DELETE触发器,当在Customers表中删除一个客户的数据时,级联删除该学客户的定货信息(即该客户在Oreders表中的数据)。
USESales
GO
CREATETRIGGERsales_customers_delete
ONCustomers
AFTERDELETE
As
DELETECustomers
FROMCustomersINNERJOINOrders
ONCustomers.客户号=Orders.客户号
该触发器也可用下列代码来进行创建:
USESales
GO
CREATETRIGGERsales_customers_delete
ONCustomers
FORDELETE
As
DELETECustomers
WHERE客户号IN(SELECT客户号FROMOrders)
3、UPDATE触发操作
UPDATE(更新)触发操作触发器的执行过程如下:
(1)执行UPDATE语句进行数据更新。
系统检查被更新的正确性(如:
约束等),如果正确,在表中修改数据。
(2)执行该表UPDATE触发器中的相应语句。
如果执行到ROLLBACK操作,则系统将回滚整个操作(恢复第一步的修改,对触发器中已经执行的操作做反操作)。
例:
为Customers表创建一个UPDATE触发器,当在Customers表中修改一个客户的客户号时,级联修改该客户在Orders表中的客户号(只针对单条记录的修改,对多条记录的修改无效)。
USESales
GO
CREATETRIGGERsales_customers_update
ONCustomers
FORupdate
AS
IFUPDATE(客户号)
BEGIN
UPDATECustomers
SET客户号=(selectOrders.客户号FROMOrdersINNERJOINCustomers
onOrders.客户号=Customers.客户号)
FROMCustomersINNERJOINOrders
ONOrders.客户号=Customers.客户号
PRINT'级联修改成功!
'
END
ELSE
PRINT'没有修改客户号!
'
4、触发操作的组合
用户可以在一个After触发器组合应用各种触发操作。
例如,我们可以在Customers表上创建一个INSERTDELETE触发器如下:
USESales
GO
CREATETRIGGERsales_insert_delete
ONCustomers
FORinsert,delete
As
IFEXISTS(select*fromOrders)
BEGIN
INSERTCustomers(客户号)
SELECT客户号FROMOrders
END
ELSE
BEGIN
DELETECustomers
FROMCustomersINNERJOINOrdersONCustomers.客户号=Orders.客户号
END
11.2.3创建INSTEADOF触发器
INSTEADOF触发器可以取代激发它的操作来执行,它在Inserted表和Deleted表刚刚建立,其他任何操作还没没有发生时被执行。
例,在Company数据库的Employee和Department表上创建一视图,该视图包括职员的代号、姓名、性别、工作部门名称和部门职能,然后在该视图上创建INSTEADOF触发器,使通过该视图可以对Employee表和Department表中的数据进行输入。
1、创建视图
USECompany
GO
CREATEVIEWview_com
AS
SELECT职员代号,职员姓名,性别,部门名称,部门职能
FROMEmployee,Department
WHEREEmployee.工作部门代号=Department.部门代号
2、在视图上创建INSTEADOF触发器
USECompany
GO
CREATETRIGGERinsert_emp_dep
ONview_com
INSTEADOFINSERT
AS
INSERTINTOEmployee(职员代号,职员姓名,性别)
Select职员代号,职员姓名,性别
FROMinserted
INSERTINTODEPARTMENT(部门名称,部门职能)
Select部门名称,部门职能
FROMinserted
11.2.4使用企业管理器创建触发器
使用企业管理器也可以实现触发器的创建,具体步骤如下:
图11-1创建触发器
(1)选中要创建触发器的表所在的数据库,展开该节点。
(2)展开该数据库节点下的“表”节点。
(3)选中指定的表,用鼠标右键单击,在弹出的快捷菜单中选择“所有任务”|“管理触发器”命令。
弹出如图11-1所示的对话框。
(4)在“文本”框中输入创建触发器的语句。
(5)单击“检查语法”按钮,检查输入的-T-SQL语句是否正确。
如果语法正确,单击“确定”按钮,完成触发器的创建。
11.2.5建立列级触发器
在通常情况下,用户对表所做的修改都只局限在表中的某些列上,而且,用户经常需要判断在某些列上的数据是否发生了修改,并在数据被修改时作出相应的反应。
这种形式的触发器,被称作列级触发器。
列级触发器主要针对某些列实施监控。
建立列级触发器的语法格式为:
CREATETRIGGERtrigger_name
ONtable
[WITHENCRYPTION]
{
{FOR{[INSERT][,][UPDATE]}
[WITHAPPEND]
[NOTFORREPLICATION]
AS
{IFUPDATE(column)
[{AND|OR}UPDATE(column)]
[,…n]
}
Sql_statement[,…n]
}
}
其中UPDATE(column)用于判断指定的某列是否经过了修改。
例如,建立一个列级触发器,用于记录修改某些关键列的数据的用户账号信息。
USENorthWind
GO
CREATETABLEwho_change
(change_datedatetime,
change_columnvarchar(50),
whovarchar(50))
GO
CREATETRIGGERtr_orderdetail_insupd
ON
[orderdetails]
FORINSERT,UPDATE
AS
IFUPDATE(UnitPrice)
BEGIN
INSERTwho_change
VALUES(getdate(),'UnitPriceupdated',user_name())
END
ELSEIFUPDATE(Quantity)
BEGIN
INSERTwho_change
VALUES(getdate(),'Quantityupdated',user_name())
END
ELSEIFUPDATE(Discount)
BEGIN
INSERTwho_change
VALUES(getdate(),'Discountupdated',user_name())
END
首先建立了记录修改者的账户所需要的表,然后开始建立触发器。
在建立过程中使用了getdate和user_name函数,来获得修改日期和执行修改操作的用户名。
11.3管理触发器
11.3.1查看触发器信息
1、查看触发器的定义
图11-2查看触发器的定义
查看触发器定义的方法有两种:
使用企业管理器和使用系统存储过程。
◆使用企业管理器
使用企业管理器查看触发器定义的步骤如下:
(1)在企业管理器的树形目录中展开要选用的服务器组、服务器。
(2)展开相应的数据库,选中“表”目录,在右窗口中触发器所在的表上单击鼠标右键,选择“所有任务”|“管理触发器”命令,打开如图11-2所示的对话框。
在“名称”下拉列表框中选择要查看定义的触发器的名称,“文本”框将显示此触发器的定义。
◆使用系统存储过程
使用系统存储过程查看触发器定义的格式为:
SP_HELPTEXTtrigger_name
例如,查看触发器sales_customers_delete的定义。
USESales
EXECsp_helptextsales_customers_delete
结果如图11-3所示:
图11-3使用系统存储过程查看触发器的定义
2、查看触发器的ID号
查看触发器的ID号的格式为:
SELECTOBJECT_ID(‘trigger_name’)
例如,查看触发器sales_customers_delete的ID号。
USESales
GO
SELECTOBJECT_ID('sales_customers_delete')
3、查看当前数据中所有定义的触发器名称
例如,查看Sales数据库中所有的触发器的名称
USESales
GO
SELECTname
FROMsysobjects
WHEREtype='TR'
4、查看触发器的类型、名称、拥有者和创建日期等
使用sp_helptrigger系统存储过程可以显示一个表上触发器的类型。
例如,查看Sales数据库中Customers表上的触发器的类型,可用以下代码来进行:
USESales
GO
sp_helptrigger Customers
从其显示结果中可以看出,其中包括了触发器的名称、拥有者,并用五个布尔值表明了触发器的类型和触发器被激活的时机。
11.3.2修改触发器
1、使用T—SQL语句
如果需要修改触发器的定义和属性,有两种方法:
先删除原来触发器的定义,再重新创建与之同名的触发器;或者直接修改现有的触发器定义。
直接修改现有的触发器定义的语法格式为:
ALTERTRIGGERtrigger_name
ON[table_name|view_name]
[WITHENCRYPTION]
{FOR|AFTER|INSTEADOF}
{[DELETE][,][INSERT][,][UPDATE]}
[NOTFORREPLICATION]
AS
sql_statement
如果原来的触发器定义是用WITHENCRYPTION或RECOMPILE创建的,那么只有在ALTERTRIGGER中也包含这些选项时,这些选项才有效。
例如,修改触发器sales_customers_delete。