数据库技术与应用：Chap05-2-触发器

1、1 数据库技术与应用数据库技术与应用 第五章第五章 触发器触发器 2Tongji University 2 5.15.1 Transact-SQLTransact-SQL语言基础语言基础 5.25.2 存储过程存储过程 ( (重点重点) ) 5.3 5.3 约束约束 5.4 5.4 触发器触发器 ( (重点重点) ) 本节内容提要本节内容提要 3Tongji University 3 保证进入保证进入 ，需要对数据库的插入、修改、删除操，需要对数据库的插入、修改、删除操 作加以检查，并将这类检查交由作加以检查，并将这类检查交由DBMSDBMS管理。管理。 SQL SQL 提供了提供了多多种表达

2、完整性约束的技术：种表达完整性约束的技术： 在创建表时，加主键、外键等在创建表时，加主键、外键等约束约束(Constraints)(Constraints)声明声明 。 当表创建后，可通过创建当表创建后，可通过创建触发器触发器(Triggers)(Triggers)来表达。来表达。 当表创建后，还可创建当表创建后，还可创建规则规则(Rules)(Rules)来表达。来表达。 引言：数据完整性引言：数据完整性 4Tongji University 4 在在CREATE TABLE CREATE TABLE 语句中可声明两类约束：语句中可声明两类约束： 1. 1.在单一属性上施加的约束在单一属性上

3、施加的约束, ,称为基于属性的约束；如，称为基于属性的约束；如， - NOT NULL：阻止该属性取值为NULL的元组进入表中。 - UNIQUE：阻止该属性取值与表中已有值相同的元组进入表。 - DEFAULT：设置属性默认取值，当未指定该项值时取默认值。 - CHECK(条件表达式)：属性取值必须满足条件，阻止不满足条件的。 - 单属性PRIMARY KEY：属实体完整性约束。实体完整性约束。 - 单属性FOREIGN KEY：属参照完整性约束。参照完整性约束。 2.2. 在一整个元组上施加约束，称为基于元组的约束；在一整个元组上施加约束，称为基于元组的约束； 如，如，多属性PRIMARY

4、 KEY、FORIGN KEY，或CHECK约束。当元 组中任意属性发生更新时，都要检查约束条件是否满足，若不满足则 拒绝。 5.3 约束约束(Constraints) 5Tongji University 5 声明外键约束的意义声明外键约束的意义 外键的声明有两重含义外键的声明有两重含义: : 1. 1. 被参照的属性必须是在所属表被参照的属性必须是在所属表( (称为主键表，称为主键表， 或父表或父表) )中声明为中声明为UNIQUEUNIQUE或或PRIMARY KEY ,PRIMARY KEY ,否否 则不可做此外键声明。则不可做此外键声明。 2.2. 外键表中外键的外键表中外键的非空取

5、值非空取值必须是主键表中被参必须是主键表中被参 照属性已存在的值。照属性已存在的值。 6Tongji University 6 为维护参照完整性，为维护参照完整性，DBMSDBMS将检查外键表将检查外键表( (引用表引用表) )和主键和主键 表表( (父表父表) )的插入、更新和删除操作，并按如下两种情况做相的插入、更新和删除操作，并按如下两种情况做相 应处理：应处理： 1. 1.对引用表做对引用表做操作时，若新行的外键取值非空操作时，若新行的外键取值非空 且在父表的被参照属性上无对应值，系统则拒绝该非法且在父表的被参照属性上无对应值，系统则拒绝该非法 更新。更新。 2.2. 对父表做对父表做

6、操作时，若更新或删除的行中某属操作时，若更新或删除的行中某属 性值被引用表中的外键引用，则该更新或删除操作将根性值被引用表中的外键引用，则该更新或删除操作将根 据所选择的策略执行据所选择的策略执行( (设计者自行选择设计者自行选择) )： a) No ACTION：是默认策略。报错，并回滚操作。 b) CASCADE：称为级联策略。引用表模仿父表的行为。 c)SET NULL：称为置空策略。父表更新或删除行，引用表对应 属性取NULL。若要执行此约束，外键列必须可为空值。 维护参照完整性维护参照完整性 7Tongji University 7 外键约束声明的两种方式：外键约束声明的两种方式：

7、1. 1. CREATE CREATE TABLETABLE中中, ,单属性外键声明单属性外键声明 FOREIGN KEY REFERENCES referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT 2. 2. CREATE TABLECREATE TABLE中，多属性外键声明中，多属性外键声明 FOREIGN KEY ( column ,.n ) REFE

8、RENCES referenced_table_name ( ref_column ,.n ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT FOREIGN KEY声明语法声明语法 8Tongji University 8 例例11. 11. 定义定义sc sc的属性的属性snumsnum为为外外键键, ,参照参照studentstudent表的主键表的主键snumsnum，该，该 外键采用级联更新外键采用级联更新和和删除

9、策略。删除策略。 CREATE CREATE TABLE TABLE sc(sc( snumsnum CHARCHAR(4)(4) NOT NULL NOT NULL REFERENCES REFERENCES student(student(snumsnum) ) On update cascade On On update cascade On delete delete cascadecascade, , secnumsecnum CHARCHAR(6) (6) NOT NOT NULLNULL, , scorescore intint checkcheck(score (score b

10、etweenbetween 0 0 andand 100),100), PRIMARY KEY PRIMARY KEY ( (snumsnum, , secnumsecnum) ) ) FOREIGN KEY设置参照完整性约束设置参照完整性约束 9Tongji University 9 用户用户根据自身根据自身需要而定义的、需要而定义的、表达特定业务规则的表达特定业务规则的 约束，约束，称为称为自定义自定义数据完整性约束数据完整性约束。 实施方法有实施方法有3 3种：种： 1.CREATE TABLE命令中的CHECKCHECK约束 2.2. 触发器触发器 3. 规则 (Microsoft S

11、QL Server 的未来版本中将删除 CREATE RULE，但直至SQL SERVER2012仍未删除) http:/ 注意注意：最初创建表时可用CHECK约束实施自定义数据完整性控 制；若表已经创建，可通过创建触发器或规则做补充。 自定义数据完整性约束自定义数据完整性约束 10Tongji University 10 1. 1. 在在CREATE TABLECREATE TABLE中，中，只能只能声明在一个元组，或声明在一个元组，或 一个元组的成员一个元组的成员( (某属性某属性) )上的约束。上的约束。 2.2. 难以难以表达复杂的数据完整性约束：表达复杂的数据完整性约束： 用户特定的

12、、复杂业务规则。 涉及多表的、复杂的数据完整性约束。 解决：解决： 触发器(TRIGGER)是SQL SERVER中功能更强的动态成员, 既可在创建表后补加数据完整性约束，又能突破CREATE TABLE中约束的局限性。对复杂的数据完整性要求，通常 使用触发器来实现。 CREATE TABLE中约束的局限性中约束的局限性 11Tongji University 11 5.4 触发器（触发器（Triggers） 触发器是数据库服务器中发生事件时触发器是数据库服务器中发生事件时自动执自动执 行的特殊存储过程行的特殊存储过程。也称作。也称作“event-condition-event-conditi

13、on- action rule”, action rule”, 与前述约束不同，有如下特点：与前述约束不同，有如下特点： 它它基于一个表或视图基于一个表或视图创建，并和一个或多个创建，并和一个或多个 数据更新数据更新(UPDATE(UPDATE、INSERT INSERT 、DELETE)DELETE)操作关操作关 联；当指定的联；当指定的事件事件(event)(event)发生时被唤醒；当测发生时被唤醒；当测 试试条件条件(condition)(condition)成立时由成立时由DBMSDBMS执行触发器相执行触发器相 关的关的动作动作(action)(action)，维护数据的完整性。，

14、维护数据的完整性。 12Tongji University 12 触发器的用途触发器的用途 触发器的主要作用是强化约束强化约束、实现复杂的完实现复杂的完 整性控制整性控制。此外，还可完成下列功能： 1. 1. 级级联操作联操作(Cascaded operation) 比如一张表的触发器可以涉及到多张表的操作。 2.2. 监听监听变化变化(Auditing changes) 安全性控制 侦测数据库内的操作，阻止未经许可的更新操作和访问。 3.3. 调用调用存储过程存储过程(Stored procedure invocation) 触发器是一个特殊存储过程，因此可调用其他存储过程。 13Tongj

15、i University 13 触发器是一个能由DBMS系统自动执行对数据库修改的语 句集。它由事件、条件、动作三个要素构成。设计时应将三者 协同考虑： u 选择事件选择事件(event)(event)：DMLDML触发器的触发事件触发器的触发事件是指对表或视图的 插入、删除、修改操作。T-SQL的DML触发器在这些事件发生 后，将开始工作。 v 考虑条件考虑条件(condition)(condition)：动作执行条件。动作执行条件。若有条件,系统将测试 条件是否成立。如果条件成立，就执行相应的动作，否则什么 也不做。 w 设计动作设计动作(action)(action)：触发动作一般是对数

16、据库的一系列操作。 如果触发器测试满足预定的条件，那么就由RDBMS执行这些 动作。 触发器的设计触发器的设计 14Tongji University 14 创建触发器创建触发器 CREATE TRIGGER 触发器名触发器名 ON 表名表名| 视图视图 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS 触发条件触发条件 触发器要完成的操作触发器要完成的操作 说明：说明： AFTER：指定触发器只有在触发事件event发生后才测试条件，执 行触发器中规定的操作。所有的引用级联操作和约束检查也必须成功 完成后，才能执行此触发器的动作。若仅指

17、定FOR关键字，则默认使 用AFTER。 INSTEAD OF：指定执行触发器的动作而不是执行触发事件本身， 从而替代触发事件相应的操作。 也称触发也称触发 动作动作 称事件称事件 触发表触发表 15Tongji University 15 在在T-SQLT-SQL中，中，事件发生事件发生意味着它本身对数据库的意味着它本身对数据库的 更新操作已被执行，这时被更新的数据存放更新操作已被执行，这时被更新的数据存放在在由由SQL SQL SERVERSERVER数据库管理系统创建数据库管理系统创建的的 中，以便在触发条件和动作中引用。中，以便在触发条件和动作中引用。 INSERTINSERT事件发生

18、时事件发生时, ,系统创建表系统创建表insertedinserted, ,并将新行存入其中。并将新行存入其中。 DELETEDELETE事件发生时事件发生时, ,系统创建表系统创建表deleteddeleted, ,并将刚删除的旧行并将刚删除的旧行 存入存入其其中。中。 UPDATEUPDATE事件发生时，系统事件发生时，系统依次依次创建创建deleteddeleted和和insertedinserted两两 个表，个表，deleteddeleted表中表中存储刚存储刚删去删去的数据行的数据行；insertedinserted表中表中存存 储新储新插入的行插入的行。 注意注意：触发器触发器执

19、行完毕后，执行完毕后，insertedinserted和和deleteddeleted表都被删除。表都被删除。 inserted inserted表和表和deleteddeleted表是由系统维护，表是由系统维护，用户不可修改。用户不可修改。 事件发生时产生的临时数据事件发生时产生的临时数据 16Tongji University 16 对任何标记为对任何标记为FORFOR INSERTINSERT的触发器，的触发器， 首先，被触发器表上的首先，被触发器表上的INSERTINSERT事件事件唤醒唤醒。此时，。此时， 触发事件本身已被执行触发事件本身已被执行, ,对对成功的请况成功的请况, ,系

20、统已将系统已将 新行插入到触发器表和新行插入到触发器表和insertedinserted表中。表中。 然后，测试条件、执行动作然后，测试条件、执行动作( (即触发器中即触发器中ASAS后的后的 语句语句) )。如果执行到如果执行到ROLLBACKROLLBACK语句，则系统将回语句，则系统将回 滚这个操作，也就是说回到事务的起点或事务内滚这个操作，也就是说回到事务的起点或事务内 的某个保存点。的某个保存点。 FOR INSERT触发器触发器 17Tongji University 17 对任何标记为对任何标记为FORFOR DELETEDELETE的触发器，的触发器， 首先，被触发器表上的首先

21、，被触发器表上的DELETEDELETE事件事件唤醒。唤醒。此时此时， 触发事件本身已被触发事件本身已被执行，对执行，对成功的请成功的请况况，系统已系统已 从触发器表中删除相应行，并已将删除的行存到从触发器表中删除相应行，并已将删除的行存到 deleteddeleted表中。表中。 然后，执行触发器中然后，执行触发器中ASAS后的语句。后的语句。如果执行到如果执行到 ROLLBACKROLLBACK语句，则系统将回滚整个操作，也就语句，则系统将回滚整个操作，也就 是说回到事务是说回到事务(DELETE)(DELETE)的起点或事务内的某个保的起点或事务内的某个保 存点。存点。 FOR DELE

22、TE触发器触发器 18Tongji University 18 对任何标记为对任何标记为FORFOR UPDATEUPDATE的触发器，的触发器， 首先，被触发器表上的首先，被触发器表上的UPDATEUPDATE操作唤醒。操作唤醒。此时此时， 触发事件本身已被执行，对成功的请况触发事件本身已被执行，对成功的请况，系统已，系统已 从触发器表中删除旧行，并将旧行复制到从触发器表中删除旧行，并将旧行复制到 deleted deleted 表中，又将新行插到了触发器表和表中，又将新行插到了触发器表和 inserted inserted 表中表中。 然后，执行触发器中然后，执行触发器中ASAS后的语句。

23、后的语句。如果执行到如果执行到 ROLLBACKROLLBACK语句，则系统将回滚整个操作。语句，则系统将回滚整个操作。 FOR UPDATE触发器触发器 19Tongji University 19 用触发器实施用触发器实施参照完整性控制参照完整性控制一一 例例12 12 建立一个insert触发器，每当在SC表中插入 一条记录时，检查学号snum和班号secnum的参照 完整性，即插入记录中的学号是否在student表中 存在、班号是否在sections表中存在，如果存在则 允许插入，否则回滚插入操作。关系模式如下： sc(snum,secnum,score) student(snum,s

24、name,sex,birthday,dept,telephone) sections(secnum,cnum,pnum) 注意：注意：先删除sc中外键约束。 20Tongji University 20 CREATE TRIGGER sc_insert ON sc FOR INSERT AS DECLARE _snum AS CHAR(4),_secnum AS CHAR(5) SELECT _snum=i.snum,_secnum=i.secnum FROM inserted i IF NOT EXISTS(SELECT * FROM student WHERE snum=_snum) BE

25、GIN PRINT 该生不存在！该生不存在！ ROLLBACK TRAN END ELSE IF NOT EXISTS(SELECT * FROM sections WHERE secnum=_secnum) BEGIN PRINT 该班号不存在！该班号不存在！ ROLLBACK TRAN END ELSE PRINT 操作成功！操作成功！ 事件事件 21Tongji University 21 测试过程：因为触发器不能主动(调用)执行，所以只能设计触 发事件来激发触发器的执行。对于插入操作,设计下列3个用例 并执行，测试触发器所有的分支情况。 -测试一: s009学号实际上不存在 INSER

26、T INTO INSERT INTO scsc VALUESVALUES( (s009s009, , 1350113501,88),88) -测试二 s008和 13501都存在 INSERT INTO INSERT INTO scsc VALUESVALUES( (s008s008, , 1350113501,88),88) 触发器测试用例触发器测试用例 -测试三 13511班号不存在 INSERT INTO INSERT INTO scsc VALUESVALUES( (s008s008, , 1351113511,88),88) 22Tongji University 22 二者功能相同

27、。如果触发器中发出 ROLLBACK 或 ROLLBACK TRAN： 将当前事务中对数据所做的修改(包括触发器 所做的修改)，回到事务的起点或事务内的某个保 存点。 触发器继续执行 ROLLBACK 或 ROLLBACK TRAN 语句之后的所有其余语句.如果这些语句中的任意 语句修改数据，则不回滚这些修改。执行其余的 语句不会激发嵌套触发器。 ROLLBACK | ROLLBACK TRAN 事务名事务名 23Tongji University 23 用触发器实施用触发器实施参照完整性控制参照完整性控制二二 例例13 13 建立一个DELETE触发器，每当在teachers表中删除 一条或

28、多条记录时，将sections表中相应记录置为NULL。 关系模式如下： teachers (pnum,pname,sex,telephone,dept) Sections(secnum,cnum,pnum) CREATE TABLE CREATE TABLE teachers(teachers( pnumpnum charchar(4) (4) PRIMARY KEYPRIMARY KEY, , pnamepname varcharvarchar(10),(10), sex sex charchar(2) (2) checkcheck( (sex sex inin( ( 男男 , , 女女

29、),), telephone telephone charchar(11) (11) checkcheck( (telephone telephone likelike 0-90-90-0-90-90- 90-90-90-90-90-90-90-90-990-90-90-90-90-90-90-90-9 ), ), deptdept varcharvarchar(20) (20) ) ) 24Tongji University 24 ALTER TRIGGER teachers_delete ON teachers FOR DELETE AS IF EXISTS(SELECT * FROM D

30、ELETED) BEGIN DECLARE _pnum AS CHAR(4); SELECT _pnum=d.pnum FROM deleted d; DECLARE d_cursor CURSOR FOR SELECT pnum FROM deleted; OPEN d_cursor; FETCH NEXT FROM d_cursor INTO _pnum; WHILE(FETCH_STATUS=0) BEGIN IF EXISTS (SELECT * FROM sections WHERE pnum=_pnum) BEGIN UPDATE sections SET pnum=null WH

31、ERE pnum=_pnum; PRINT 已更新班级表，置空已更新班级表，置空 + _pnum + 授课班级工号！授课班级工号！; END ELSE PRINT 班级表中不存在该教师记录！班级表中不存在该教师记录！; FETCH NEXT FROM d_cursor INTO _pnum; END CLOSE d_cursor; DEALLOCATE d_cursor; END ELSE PRINT 教师表中不存在该记录！教师表中不存在该记录！ 事件事件 条件条件 动作动作 25Tongji University 25 -测试一: p008开了1个班课 DELETE DELETE FROM

32、FROM teachersteachers WHERE WHERE pnumpnum likelike p01%p01% -测试二: p009没开课 DELETEDELETE FROM FROM teachersteachers WHERE WHERE pnumpnum= =p009p009 teachers_delete触发器的作用是在删除teachers表的教师记 录的同时将班级表sections的相关记录pnum属性置空，以维护 数据的参照完整性。测试用例设计如下： 触发器测试用例触发器测试用例 -测试三: p012不在教师表中 DELETEDELETE FROM FROM teache

33、rsteachers WHERE WHERE pnumpnum= =p012p012 26Tongji University 26 用触发器实施用触发器实施自定义数据完整性自定义数据完整性控制控制 例例1414 建立一个UPDATE触发器，每当在SC表中修改一条记录的 分数时，若使得总成绩平均分小于70，则拒绝修改。关系模式 如下：sc(snum,secnum,score) CREATE TRIGGER sc_update ON sc FOR UPDATE AS DECLARE _secnum as char(5) SELECT _secnum=i.secnum FROM inserted i

34、 IF 70 (SELECT avg(score) FROM sc WHERE secnum=_secnum) BEGIN ROLLBACK PRINT 更新失败！更新失败！ END 27Tongji University 27 -测试用例一 UPDATEUPDATE scsc SETSET scorescore=0=0 WHERE WHERE snumsnum= =s002s002 and and secnumsecnum= =1320113201 执行结果执行结果 -测试用例二 UPDATEUPDATE scsc SETSET scorescore=97=97 WHEREWHERE snu

35、msnum= =s002 s002 and and secnumsecnum= =1320113201 执行结果执行结果 28Tongji University 28 用触发器实现用触发器实现级级联操作联操作 例例15 15 建立一个触发器，每当在SC表中修改一条记录的成绩或班 号时(假定不该学号)，也将在sec_avg表中更新相应的记录。关 系模式如下： sc(snum,secnum,score) sec_avg(secnum,avg) SC表表 sec_ avg表表 更新前的数据表 29Tongji University 29 ALTER TRIGGER sec_update ON sc

36、FOR UPDATE AS DECLARE _secnum as char(5),_dsecnum as char(5) SELECT _secnum=i.secnum FROM inserted i SELECT _dsecnum=d.secnum FROM deleted d IF (_secnum=_dsecnum) BEGIN UPDATE sec_avg SET avg=(SELECT AVG(score) FROM sc WHERE secnum=_secnum) WHERE secnum=_secnum; PRINT 成功更新班级平均成绩！成功更新班级平均成绩！ END ELSE

37、 IF NOT EXISTS (SELECT * FROM sec_avg WHERE secnum=_secnum) BEGIN INSERT INTO sec_avg SELECT secnum,AVG(score) FROM sc WHERE secnum=_secnum GROUP BY secnum; PRINT 新增加了一条班级记录！新增加了一条班级记录！; UPDATE sec_avg SET avg=(SELECT AVG(score) FROM sc WHERE secnum=_dsecnum) WHERE secnum=_dsecnum END ELSE BEGIN UPD

38、ATE sec_avg SET avg=(SELECT AVG(score) FROM sc WHERE secnum=_dsecnum) WHERE secnum=_dsecnum; UPDATE sec_avg SET avg=(SELECT AVG(score) FROM sc WHERE secnum=_secnum) WHERE secnum=_secnum; PRINT 成功更新班级记录！成功更新班级记录！ END 30Tongji University 30 触发器测试用例的设计触发器测试用例的设计 测试用例的设计：当修改SC表中学生的选课记录时，有两种 情况要考虑。 1. 1.

39、 在sc中修改一学生的某门已修课程的成绩，则sec_avg中该 课程的平均成绩要跟着修改； 2.2. 某学生的班号登记错了，并且进行修改后的班号在表 sec_avg中不存在，这时需要在触发动作中对sec_avg表中作 插入操作和修改操作；若修改后班号在sec_avg中存在，则对 sec_avg表中作两次修改操作。 31Tongji University 31 -测试一:改成绩 UPDATE UPDATE scsc SET SET scorescore=70=70 WHERE WHERE snumsnum= = s001s001 AND AND secnumsecnum= = 132011320

40、1 执行结果： (1 行受影响 ) 成功更新班级平均成绩！ (1 行受影响 ) 更新后的数据表 sec_ avg表 SC表 32Tongji University 32 -测试二: 改班号,插入新班平均成绩,修正原记录 UPDATEUPDATE scsc SET SET secnumsecnum= = 1320213202 WHERE WHERE snumsnum= =s001s001 AND AND secnumsecnum= =1320113201 执行结果： (1 行受影响) 新增加了一条班级记录！ (1 行受影响) (1 行受影响) SC表 sec_ avg表 测试二运行后表的内容 3

41、3Tongji University 33 -测试三:改班号,前后都是已有的班号 UPDATE UPDATE scsc SET SET secnumsecnum= =1320213202 WHERE WHERE snumsnum= =s004s004 ANDAND secnumsecnum= =1320113201 执行结果： (1 行受影响) (1 行受影响) 成功更新班级记录！ (1 行受影响) SC表 sec_ avg表 测试三运行后表的内容 34Tongji University 34 INSTEAD OF 触发器触发器 表触发器不同，视图触发器的定义使用表触发器不同，视图触发器的定义

42、使用INSTEAD OFINSTEAD OF关关 键字替代键字替代FOR|AFTERFOR|AFTER。其作用原理：。其作用原理： 一个视图触发器被对一个视图触发器被对该该视图上的触发事件视图上的触发事件(INSERT(INSERT、 DELETEDELETE、UPDATEUPDATE操作操作) )所唤醒，替代对该视图本身的操所唤醒，替代对该视图本身的操 作作，系统将执行这个视图触发器的动作系统将执行这个视图触发器的动作(AS(AS后的后的SQLSQL语句语句) )， 实现对该视图所引用的基本表中数据更新，从而使此视图实现对该视图所引用的基本表中数据更新，从而使此视图 也得以更新也得以更新。

43、INSTEAD INSTEAD OF OF 触发器触发器的优点：的优点：可以可以使使不不可可更新更新视图视图得得 以以更新更新。包含多个数据表的。包含多个数据表的视图是不可更新视图，因此必视图是不可更新视图，因此必 须须使用使用 INSTEAD OF INSTEAD OF 触发器触发器来完成对引用来完成对引用表中数据的更新表中数据的更新 操作操作。 35Tongji University 35 表表sc 例例1616 建立一个INSTEAD OF INSERT触发器，每当对视图 view_score执行插入操作时，在视图所引用的基本表中插入 相应数据。 视图视图view_score 用用INS

44、TEAD OF触发器更新多表视图触发器更新多表视图 CREATE VIEW view_score AS SELECT snum,sc.secnum,cnum,score FROM sc JOIN sections on sc.secnum=sections.secnum 36Tongji University 36 CREATE TRIGGER instead_of_insert ON view_score INSTEAD OF INSERT AS DECLARE _snum CHAR(4),_secnum CHAR(5),_cnum CHAR(4),_score INT SELECT _sn

45、um= i.snum,_secnum=i.secnum,_cnum=um,_score=i.score FROM inserted i IF NOT EXISTS(SELECT * FROM student WHERE snum=_snum) RAISERROR(输入学号错误，插入失败！输入学号错误，插入失败！,1,1,ERROR) ELSE IF NOT EXISTS(SELECT * FROM course WHERE cnum=_cnum) RAISERROR(输入课号错误，插入失败！输入课号错误，插入失败！,1,1,ERROR) ELSE BEGIN IF NOT EXISTS(SEL

46、ECT * FROM sections where secnum=_secnum) BEGIN INSERT INTO sections(secnum,cnum) Values(_secnum,_cnum) PRINT 成功向成功向sections表中插入一条记录！表中插入一条记录！ END INSERT INTO sc VALUES(_snum,_secnum,_score) PRINT 成功向成功向sc表中插入一条记录！表中插入一条记录！ END 37Tongji University 37 触发器测试用例的设计触发器测试用例的设计 测试用例的设计：向view_score(snum,sec

47、num,cnum,score)视 图中插入一条记录的事件发生后，应先做如下两项检查： 1) 1) 检查新插入的学号snum是否在student中存在。若不存在， 则应拒绝插入。 2) 2) 检查新插入的课号cnum是否在course中存在。若不存在， 也应拒绝插入。 当1)和2)都满足时，再检查新插入的班号secnum是否在 sections表中存在。若不存在，应先插入该(secnum,cnum)记 录到sections表中，再将新记录(snum,secnum,score)插入sc表 中；否则直接将新记录(snum,secnum,score)插入sc表中。 38Tongji University 38 -测试一：插入学号在student表中不存在 INSERT INSERT INTO INTO view_scoreview_score VALUESVALUES( (s011s011, ,1160111601, ,c116c116,90),90) 执行结果： 输入学号错误，插入失败！ 消息50000，级别1，状态1 (1 行受影响) -测试二：插入课号cnum在course表中不存在 INSERT INSERT INTO INTO view_scorevie