第七章触发器

1、第七章第七章触发器回顾 在SQL Server 2005数据库中，( )可以被看成为虚拟的表。(选择一项)A.函数B.视图C.索引D.存储过程B create view view_name (select 语句语句)as 索引的类型可以分为哪些？索引的类型可以分为哪些？主键索引、聚集索引、非聚集索引、唯一索引主键索引、聚集索引、非聚集索引、唯一索引预习检查触发器中有两个特殊的表，分别是？插入表插入表inserted和删除表和删除表deleted 触发器类型分为： INSERT触发器 UPDATE触发器 DELETE触发器 创建删除触发器的语法：创建删除触发器的语法：create trigger

2、 trigger_nameon table_namefor delete assql语句语句本章任务当在当在recordInfo表上插入一行记录时（表示用户上机），自动更新表上插入一行记录时（表示用户上机），自动更新PCInfo表的表的PCuse列为列为1（表示电脑状态正在使用）（表示电脑状态正在使用）本章目标了解触发器的概念了解触发器的概念理解触发器的工作原理理解触发器的工作原理掌握创建掌握创建insert、delete、update触发器触发器掌握并使用掌握并使用inserted和和deleted两张表两张表q 为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统 为什么需

3、要触发器演示： 为什么需要触发器.sql帐户信息表bank 交易信息表transInfo 张三取钱200 问题：没有自动修改张三的余额最优的解决方案就是采用触发器：l它是一种特殊的存储过程 l也具备事务的功能 l它能在多表之间执行特殊的业务规则 张三开户1000元,李四开户1元 张三张三李四李四王五王五赵二赵二王三王三宋二宋二刘五刘五插入什么是触发器删除触发器触发赵二退休 赵二赵二员工表退休员工表q触发器是在对表进行插入、更新或删除操作时自动执行的存储过程q触发器通常用于强制业务规则q触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 q可执行复杂的SQL语句（if/while/

4、case）q可引用其它表中的列 什么是触发器q触发器定义在特定的表上，与表相关q自动触发执行q不能直接调用q是一个事务（可回滚）什么是触发器触发器的类型DELETE 触发器INSERT 触发器UPDATE 触发器q 触发器触发时：q系统自动在内存中创建deleted表或inserted表q只读，不允许修改；触发器执行完成后，自动删除q inserted 表 q临时保存了插入或更新后的记录行 q可以从inserted表中检查插入的数据是否满足业务需求q 如果不满足，则向用户报告错误消息，并回滚插入操作q deleted 表q临时保存了删除或更新前的记录行 q可以从deleted表中检查被删除的数

5、据是否满足业务需求q 如果不满足，则向用户报告错误消息，并回滚插入操作 inserted 和deleted 表 inserted 和deleted 表的功能修改操作修改操作inserted表表deleted表表增加(INSERT)记录存放新增的记录-删除(DELETE)记录-存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息q创建触发器的语法： 如何创建触发器CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE

6、AS T-SQL语句GOWITH ENCRYPTION表示加密触发器定义的SQL文本DELETE, INSERT, UPDATE指定触发器的类型INSERT 触发器示例问题：问题：当会员号为001上机时：向记录信息表(recordInfo)中插入一条上机信息时，我们应根据电脑的编号自动更新电脑的使用状态分析：分析：l在记录信息表上创建INSERT触发器 l从inserted临时表中获取插入的数据行l根据电脑编号(PCId)修改电脑状态是否为使用状态，l 修改电脑状态。 -关键代码-/*创建insert触发器，在上网记录表recordInfo上创建插入触发器*/create trigger tr

7、_insert_recordInfoon recordInfofor insert as/*定义变量，用于临时存储插入的会员号、电脑编号和卡的编号*/declare cardId char(10)declare PCId intdeclare CardNumber char(10)/*从inserted临时表中获取插入的记录行的信息，包括电脑的编号、卡的编号*/select PCId=PCId,cardId=CardId from inserted/*根据电脑编号修改电脑的使用状态*/update PCInfo set PCUse=1 where PCId=PCId/*根据卡的编号查询会员号*

8、/select CardNumber=CardNumber from cardinfo where CardId=cardid/*显示上机成功的信息*/print 上机成功！会员号是：+CardNumber+机器号是：+convert(char(10),PCId)goINSERT 触发器示例-插入测试数据，会员号为的上机set nocount on -不显示sql语句影响的记录行数declare CardId int -声明一个存储卡的编号的变量-根据会员号查处卡的编号select cardId=cardid from cardinfo where cardNumber=c001 -向reco

9、rdInfo表中插入一条记录信息，卡的编号、电脑编号和上机时间 insert into recordInfo(cardId,PCId,beginTime) values(cardId,1,getDate()-查看结果select * from recordInfoselect * from PCInfoINSERT 触发器示例问题：问题：当删除记录表时，要求自动备份被删除的数据到表backRecordInfo中 。分析：分析：l在记录表上创建DELETE触发器 l被删除的数据可以从deleted表中获取DELETE触发器示例-关键代码-创建delete触发器,在上网记录表RecordInfo上

10、创建删除触发器create trigger tr_delete_recordInfoon recordInfofor deleteasif exists(select * from sysobjects where name=backRecordInfo) -如果backrecordInfo表存在，就添加记录即可 insert into backRecordInfo select * from deletedelse -创建backRecordInfo表，从deleted中获取被删除的数据 select * into backRecordInfo from deletedprintbackRe

11、cordInfo表备份数据成功，备份表中的数据为： select * from backRecordInfogo从deleted表中获取被删除的交易记录DELETE触发器示例DELETE触发器示例-关键代码-测试delete触发器，删除数据set nocount ondelete from recordInfo-查看结果print记录表中的数据为：select * from recordInfoUPDATE触发器示例问题：问题：在网吧计费系统中，有时我们需要对电脑进行换机操作，这样我们就要更新recordInfo（记录表）中的上机记录。 分析：分析：l当一台电脑空闲时，我们需要修改当前电脑的使

12、用状态为0（正常状态），当一台电脑正在使用时，我们需要修改当前电脑的使用状态为1（使用状态）；当修改记录表时，要获取电脑的信息。l为记录表创建update触发器，这样就可以同时修改电脑的状态信息。UPDATE触发器示例-关键代码-create trigger tr_update_recordInfoon recordInfofor updateasdeclare beforePCId intdeclare afterPCId intselect beforePCId =PCId from deletedselect afterPCId=PCID from inserted-根据电脑编号修改使用

13、状态-根据以前使用的电脑编号把电脑的使用状态改为： update PCInfo set PCUse=0 where PCId=beforePCId-根据现在使用的电脑编号把电脑的使用状态改为: update PCInfo set PCUse=1 where PCId=afterPCId-显示电脑换机成功print换机成功！从+convert(varchar(10),beforePCId)+号电脑换到+convert(varchar(10),afterPCId)+号电脑goUPDATE触发器示例-关键代码-/*测试update触发器，修改电脑编号*/-显示更改前,记录表中的数据print更改前，

14、记录表中的数据select * from recordInfo-显示更改前，电脑表中的数据print更改前，电脑表中的数据select * from PCInfoset nocount on-把电脑号为1的改为2update recordInfo set PCId=2 where PCId=1 -查看结果print更改后，记录表中的数据select * from recordInfoprint更改后，电脑表中的数据select * from PCInfoUPDATE触发器示例小结当更新表中某列或多列时触发，自动执行触发器中定义的SQL语句。insert触发器：触发器：delete触发器：触发器

15、：update触发器：触发器：当向表中插入数据时触发，自动执行触发器中定义的SQL语句。当删除表中记录时触发，自动执行触发器中定义的SQL语句。列级 UPDATE 触发器update触发器无论是对表中的一行或多行，还是一列或多列，都将执行触发器操作。但是在实际需求中，可能只关心对特定列是否被更新。如果特定的列被更新，就执行触发器操作。这可以通过列上的update语法：if update来实现。在同一个触发器的定义语句中，可以使用多条if update 语句来对不同的列的修改执行不同的触发器操作。例如，在网吧计费系统中上机时间一般由系统自动产生，默认为当前时间。为了防止非法人员有机可乘（作弊等）

16、，一般禁止修改。如何防止用户误操作而修改了上机时间，我们可以使用update（列名）函数来检测是否修改了某列。T-SQL 代码如下：-关键代码-创建update触发器，在上网记录表recordInfo上创建修改（列）触发器create trigger tr_updateColum_recordInfoon recordInfofor updateas-检查是否修改了上机时间（beginTime）if update(beginTime)begin print修改失败！ raiserror(安全警告：上机时间不能修改，由系统自动产生,16,1) rollback transaction -回滚操作

17、，撤销操作endgo列级 UPDATE 触发器-关键代码-set nocount on-把上机时间修改为-5update recordInfo set beginTime=2010-6-5列级 UPDATE 触发器instead of触发器的使用instead of触发器的使用范围,instead of 触发器可以同时在数据表和视图中使用。通常在以下几种情况下,建议使用instead of触发器:数据库里的数据禁止修改:例如电信部门的通话记录是不能修改的,一旦修改,则通话费用的计数将不准确。有可能要回滚修改的SQL语句在视图中使用触发器用自己的方式去修改数据instead of触发器的使用例如

18、，在网吧计费系统中当用户的余额大于2元时才能上机，否则给出提示信息。在recordInfo表上创建instead of触发器，向recordInfo表插入上机信息时执行触发操作。instead of触发器示例-关键代码-创建update触发器，在上网记录表recordInfo上创建修改（列）触发器create trigger tr_updateColum1_recordInfoon recordInfoinstead of insertas declare cardbalance int -声明用于存储用户余额的变量 declare CardId int -声明用于存储用户卡的编号的变量 de

19、clare PCId int -声明用于存储电脑编号的变量-inserted临时表中获取插入的记录行信息，包括电脑编号、卡的编号临时表中获取插入的记录行信息，包括电脑编号、卡的编号select cardId=cardId,PCId=PCId from insertedselect cardbalance=cardBalance from cardInfo where CardId=CardIdprint您的余额为：+convert(varchar(10),cardBalance) -打印余额信息if(cardBalance2) -判断余额多少，看能否正常上机判断余额多少，看能否正常上机prin

20、t余额小于元，不能上机。请尽快充值！else-根据电脑的编号修改电脑的使用状态更改为正在使用 update PCInfo set PCUse=1 where PCId=PCId-向recordInfo表插入上机记录insert into recordInfo(cardId,PCId,beginTime)values(CardId,PCId,getdate() print上机成功goinstead of触发器示例-关键代码-set nocount ondeclare cardId int -声明一个存储卡的编号的变量-根据会员号查出卡的编号select cardId=cardId from ca

21、rdInfo where cardNumber=c001-向recordInfo表中插入一条记录信息，卡的编号、电脑的编号和上机时间insert into recordInfo(cardId,PCId,beginTime)values(cardId,1,getdate()select * from recordInfoselect * from PCInfo小结instead of触发器的创建语法？create trigger trigger_nameon table_nameinstead of insert assql语句语句使用触发器的优缺点使用触发器的优点：使用触发器的优点： 触发器可

22、以强制比用触发器可以强制比用check约束定义的约束更为复杂的约束。约束定义的约束更为复杂的约束。 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一张触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一张表中的多个同类触发器（表中的多个同类触发器（insert、update或或delete）允许采取多个不同的）允许采取多个不同的对策以响应同一条修改语句。对策以响应同一条修改语句。触发器的主要作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的触发器的主要作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。一致性。使用触发器的缺点：使用触发器的缺点：触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有错，但由于我们的滥用会造成数据库及应用程序的维护困触