《大型数据库应用与维护》课件-第20讲 触发器

大型数据库应用与维护以SQLServer2012为例上次内容回顾回顾7.1存储过程7.1.1存储过程的定义7.1.2存储过程的优点7.1.3存储过程的分类7.1.4常用的系统存储过程7.1.5创建与调用存储过程7.1.6管理存储过程7.1.7存储过程提高存储过程内容提问4、带输出参数的存储过程，在参数类型后面要加哪个关键字？（1分）5、常见的存储过程管理有哪些？（1分）3、常见的用户自定义存储过程有哪几类？（1分）回顾1、存储过程是什么？（1分）2、使用存储过程有什么优势？（1分）6、存储过程提高中我们讲了哪些知识点？（1分）上节内容总结回顾存储过程（StoredProcedure）：是在大型数据库系统中，一组为了完成特定功能的SQL语句集。1、创建存储过程

CreateProc[edure]<存储过程名>[<@参数名1>…]

AS<表达式>2、执行存储过程

Exec[ute]<存储过程名>[<@参数名1>…]3、删除存储过程

DROPPROCEDURE<存储过程名>预编译指定名称、参数可选创建存储过程执行存储过程完成任务删除存储过程不用了新知识导入导入问题：“课程改名的问题”？创建存储过程进行Update操作提示不能更新是否该课程是否被使用手动执行存储过程能否自动执行？？《企业级数据库的安装、配置和管理》《SQLServer数据库应用与维护》新知识导入导入小故事：“青蛙与开水”or“火箭发射”提问：两个小故事有什么共同特点？“加热”--》青蛙起跳“点火”--》火箭发射本讲主要内容第20讲触发器的定义和类型触发器的设计与应用

知识目标能力目标重点：触发器的应用

难点：触发器的设计

7.2触发器第20讲

触发器17.2触发器7.2.1触发器的定义与类型7.2.2INSERTED表与DELETED表7.2.3创建触发器7.2.4触发器的设计与应用7.2.1触发器的定义与类型7.2.1

触发器的定义与类型1.触发器的定义触发器（trigger）是SQLserver提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。想一想：上学期讲过的数据完整性有哪些？7.2.1

触发器的定义与类型2.触发器的类型SQLServer包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。动一动：请查询DML、DDL和登录触发器三者分别指的是？有什么区别？DML触发器Insert触发器Delete触发器Update触发器

7.2.1

触发器的定义与类型3.触发器的特点不能直接调用，只有在对触发器表的数据进行更改时，才自动执行；不能传递和接受参数；触发器可以实施更为复杂的数据完整性约束。7.2.2INSERTED表与DELETED表7.2.2INSERTED表与DELETED表触发器触发时：系统自动在内存中创建deleted表或inserted；只读，不允许修改；触发器执行完成后，自动删除。7.2.2INSERTED表与DELETED表1.inserted表临时保存了插入或更新后的记录行；可以从inserted表中检查插入的数据是否满足业务需求；如果不满足，则向用户报告错误消息，并回滚插入操作。2.deleted表临时保存了删除或更新前的记录行；可以从deleted表中检查被删除的数据是否满足业务需求；如果不满足，则向用户报告错误消息，并回滚删除操作。7.2.2INSERTED表与DELETED表修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录更新(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息7.2.3创建触发器7.2.3创建触发器创建触发器的语法：CREATETRIGGER

trigger_nameONtable_name[WITHENCRYPTION]FOR[DELETE,INSERT,UPDATE]AS

T-SQL语句GOWITHENCRYPTION表示加密触发器定义的SQL文本DELETE,INSERT,UPDATE指定触发器的类型7.2.3INSERT触发器的工作原理：transInfocardIDtransTypetransMoney1001000210010002存入300存入500insertedcardIDtransTypetransMoney10010001

支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作创建触发器deletedcardIDtransTypetransMoney10010001支取

2007.2.3DELETE触发器的工作原理：transInfocardIDtransTypetransMoney1001000210010002存入300存入500删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作创建触发器7.2.3UPDATE触发器的工作原理：Deleted（更新前的数据）customerNamecardIDcurrentMoney李四100000021bankcustomerName

cardID

currentMoney张三

100100011000

李四100000021删除记录行向deleted表中插入被删除的副本检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作李四

1000000220001向inserted表中插入被添加的副本Inserted（更新后的数据）customerNamecardIDcurrentMoney李四1000000220001插入记录行创建触发器7.2.3DML中三类触发器的工作原理小结创建触发器INSERT触发器工作原理DELETE触发器工作原理UPDATE触发器工作原理执行insert插入语句，在表中插入数据行触发insert触发器，向inserted表中插入新行的备份（副本）触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作执行delete删除语句，删除表中的数据行触发delete触发器，向deleted表中插入被删除的副本触发器检查deleted表中插入的新行数据，确定是否需要回滚或执行其他操作执行update更新语句触发update触发器，删除原有的数据，将数据备份到deleted表中再插入新行，将数据备份到inserted表中。检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作7.2.4触发器的设计与应用7.2.4触发器的设计与应用问题：“课程改名的问题”？“抛问题，留悬念”“解问题，去悬念”

创建存储过程进行Update操作提示不能更新是否该课程是否被使用手动执行存储过程创建UPDATE触发器回滚更新操作是该课程是否被使用当进行UPDATE操作的时候，会自动调用UPDATE触发器7.2.4触发器的设计与应用例：设计一个触发器（

SC_update

），当向课程表中修改课程名称时触发该触发器，如果该课程正在被选修，则课程名不能进行修改。CREATETRIGGERSC_updateON课程表FORUPDATEAS

beginIF(SELECTCOUNT(*)FROMinserted,选课表

WHERE选课表.课程号=inserted.课程号)>0

ROLLBACKTRANSACTION

end--指定所监控的表--指定触发事件类型--执行代码if

update(课程名)begin

if

exists(select

*

from

inserted

inner

join

选课表

on

inserted.课程号=选课表.课程号)

rollback

transactionend7.2.4触发器的设计与应用课程号课程名教师周课时数备注1软件工程吴老师42ASP程序设计黄老师43COM技术黄老师24Delphi涂老师6课程表选课表学号课程号成绩1031231199103123126010312313501031231488在UPDATE触发器执行时，产生两个特殊的临时表，inserted表和deleted表，这两个表在结构上与触发器所关联的表一样，它们可用于触发器的条件测试。7.2.4触发器的设计与应用CREATETRIGGERSC_updateON课程表FORUPDATEAS

begin