数据库触发器学习教案

1、数据库触发器数据库触发器第一页，共29页。第1页/共28页第二页，共29页。问题(wnt)的引入增加一个(y )记录删除一个记录(“95003”,”002”,56)95001 006 9041l在学生管理数据库中,某学生的选课门数是一个经常访问的属性,因此在student表中增加一个属性sselnum (smallint）l在实际应用l实例展示第2页/共28页第三页，共29页。l可否(kfu)用嵌套的SQL语句来实现?l问题的解决：使用触发器triggerlupdate student set sselnum=(select count(*) from s_c where s_c.sno=st

2、udent.sno)l存在的问题l触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性 第3页/共28页第四页，共29页。l 触发器触发原因(yunyn)对表进行插入、更新或删除操作触发事件。l 触发结果：另一段程序的执行。l 思考 ：在上例中，触发原因是什么？触发结果是什么？第4页/共28页第五页，共29页。l 触发器和存储(cn ch)过程的区别：INSERT INTO s_c VALUES(95001,006,90) EXEC Getgrade 95001 触发器可以用于 SQL Server 约束(yush)、默认值和规则的完整性检查。 触发器主要是通过

3、事件触发而执行的，而存储过程是通过调用存储过程名称而执行的。第5页/共28页第六页，共29页。第6页/共28页第七页，共29页。触发器可以实现比较复杂的完整性约束：l 扩展约束、默认值和规则对象的完整性检查。l 自动生成数据。l 检查数据的修改，防止对数据不正确的修改，保证数据表之间数据的正确性和一致性。l 自定义复杂的安全(nqun)权限。 触发器作为一种(y zhn)数据库对象，在syscomment系统表中存储其完整的定义信息，在sysobject系统表中有该对象的记录。第7页/共28页第八页，共29页。1）触发器自动执行(zhxng) 在对表的数据作了任何修改（比如手工输入或者应用程序

4、采取的操作）之后立即被激活。3）触发器可以实现比CHECK约束更为复杂的数据完整性约束 在数据库中为了(wi le)实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。 2）触发器能够对数据库中的相关表实现级联更改 触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。第8页/共28页第九页，共29页。触发器的分类(fnli)lINSTEADOF触发器:l并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。l既可在表上定义INSTEADOF触发器

5、，也可以在视图上定义INSTEADOF触发器。第9页/共28页第十页，共29页。 Inserted和Deleted是逻辑（概念）表。当在定义了触发器的表上发生(fshng)了修改操作时会自动派生出这两个视图。 当在表上发生插入操作时，新插入的行将出现在插入视图中。 当在表上发生删除操作时，被删除的行将出现在删除视图中。 当在表上发生更新操作时，旧行出现在删除视图中，新行出现在插入视图中。第10页/共28页第十一页，共29页。S_C表第11页/共28页第十二页，共29页。v触发器的基本操作创建v在创建触发器前，必须注意以下几点：v1、CREATE TRIGGER 语句必须是批处理中的第一条语句。

6、v2、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。v3、表的所有者具有创建触发器的默认(mrn)权限，不能将该权限转给其他用户。v4、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但是不能引用系统表。第12页/共28页第十三页，共29页。创建触发器命令(mng lng)格式：CREATE TRIGGER trigger_name ON tabel FOR | AFTER | INSTEAD OF INSERT,UPDATE,DELETE AS IF UPDATE(column) AND|OR UPDATE (column) sql_statement 一、企业(q

7、y)管理器二、查询分析器第13页/共28页第十四页，共29页。说明(shumng)：第14页/共28页第十五页，共29页。例：当在仓库表上发生插入操作时会输出(shch)“插入了一个仓库元组”的信息。USE仓库(cngk)管理GOCREATETRIGGERWH_INON仓库(cngk)FORINSERTASPRINT插入了一个仓库(cngk)元组INSERT仓库VALUES(WH6,南京,700)第15页/共28页第十六页，共29页。例：在仓库表上定义一个删除(shnch)触发器，使得当删除(shnch)仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL。CREATETRIGGE

8、RWH_DELON仓库(cngk)FORDELETEASUPDATE职工SET仓库(cngk)号=NULLWHERE仓库(cngk)号IN(SELECT仓库(cngk)号FROMdeleted)DELETE仓库WHERE仓库号=WH6仓库（仓库号，城市，面积）职工（仓库号，职工号，工资）第16页/共28页第十七页，共29页。例：对S表定义一个(y)删除触发器，使得当删除学生记录时，将S_C表中相应的选课记录删除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）第17页/共28页第十八页，共29页。例：对职工表定义一个插入触发器，使得当插入职工记录(jl)时，检查所参照的仓库元组是否

9、存在，如果不存在，则撤消所做的插入操作。仓库（仓库号，城市，面积(minj)）职工（仓库号，职工号，工资）第18页/共28页第十九页，共29页。CREATETRIGGERWH_INSON职工(zhgng)FORINSERTASIFNOTEXISTS(SELECT*FROM仓库WHERE仓库号=(SELECT仓库号FROMINSERTED)BEGINRAISERROR(非法仓库号!,1,1)ROLLBACKTRANSACTIONENDINSERT职工(zhgng)VALUES(WH5,E10,1600)IN第19页/共28页第二十页，共29页。例：对S_C表定义一个插入触发器，使得当插入选课记录

10、时，检查所参照的学生学号和课程号是否(shfu)存在，如果不存在，则撤消所做的插入操作。S（SNO,SNAME）C（CNO，CNAME）S_C（SNO,CNO,SCORE）第20页/共28页第二十一页，共29页。例：对职工表定义一个更新触发器，使得当职工变换所属仓库时，检查所参照的仓库元组是否存在，如果不存在，则撤消所做的更新操作(cozu)，如果新的仓库号是WH2则将工资提高10%。第21页/共28页第二十二页，共29页。CREATETRIGGERTR_UPDATEON职工FORUPDATEASDECLAREWHNOCHAR(4)IFUPDATE(仓库(cngk)号)BEGINIFNOTEX

11、ISTS(SELECT*FROM仓库(cngk)WHERE仓库(cngk)号=(SELECT仓库(cngk)号FROMINSERTED)BEGINRAISERROR(非法仓库(cngk)号!,16,1)ROLLBACKTRANSACTIONENDELSEBEGINSELECTWHNO=仓库(cngk)号FROMINSERTEDIFWHNO=WH2UPDATE职工SET工资=工资*1.1WHERE职工号=(SELECT职工号FROMINSERTED)ENDEND第22页/共28页第二十三页，共29页。例：当插入或更新学生成绩时，触发器检查(jinch)该课程是否为考查课，若是，则通过的成绩只能以

12、60分计，未通过的只能以40分计。涉及(shj)的表结构：C1(Cno,Cname,Ctype) S_C(Sno,Cno,Score) 第23页/共28页第二十四页，共29页。CREATETRIGGERSCORE_KCONS_CFORINSERT,UPDATEASDECLARESCOREINT,CTYPECHAR(4)SELECTSCORE=SCORE,CTYPE=CTYPEFROMC1,INSERTEDWHEREINSERTED.CNO=C1.CNOIF(CTYPE=考查)AND(SCORE60ANDSCORE40)BEGINRAISERROR(该课程(kchng)为考查课，成绩以60或40

13、计!,16,1)ROLLBACKTRANSACTIONEND第24页/共28页第二十五页，共29页。CREATETRIGGERSCORE_KCONS_CFORINSERT,UPDATEASDECLARESCOREINT,CTYPECHAR(4)SELECTSCORE=SCORE,CTYPE=CTYPEFROMC1,INSERTEDWHEREINSERTED.CNO=C1.CNOIF(CTYPE=考查(koch)BEGINIFSCORE=60UPDATES_CSETSCORE=60WHERECNO=(SELECTCNOFROMINSERTED)ANDSNO=(SELECTSNOFROMINSERTED)ELSEUPDATE