SQL Server2000实验指导NEW实验9触发器

1、实验实验 9 9 触发器触发器 在在SQL ServerSQL Server中，触发器同存储过程一样重要，它们都是用中，触发器同存储过程一样重要，它们都是用TranactTranact-SQL -SQL 语言所编写的程序。通过本实验来学习、掌握触发器的创语言所编写的程序。通过本实验来学习、掌握触发器的创建、修改、删除、执行的方法。建、修改、删除、执行的方法。【知识要点【知识要点】1 1触发器（触发器（TriggerTrigger） 触发器（触发器（triggertrigger）是一种特殊类型的存储过程，它也是用）是一种特殊类型的存储过程，它也是用TranactTranact- -SQL SQL

2、 语言编写的程序。语言编写的程序。 存储过程是由用户利用命令存储过程是由用户利用命令EXECUTEEXECUTE执行它，而触发器是在用户要对执行它，而触发器是在用户要对某一表内的数据做插入某一表内的数据做插入INSERTINSERT、更新、更新UPDATEUPDATE、删除、删除DELETEDELETE时被触发执行。时被触发执行。通常我们使用触发器来检查用户对数据库表的更新是否合乎整个应用系通常我们使用触发器来检查用户对数据库表的更新是否合乎整个应用系统的需求和合乎商业规则以维持表内数据的完整性和正确性。统的需求和合乎商业规则以维持表内数据的完整性和正确性。2 2触发器的作用触发器的作用 （1

3、 1）触发器可通过数据库中的相关表实现级联更改。）触发器可通过数据库中的相关表实现级联更改。通过级联引用完整性约束可以更有效地执行这些更改。通过级联引用完整性约束可以更有效地执行这些更改。 （2 2）触发器可以强制比用）触发器可以强制比用 CHECK CHECK 约束定义的约束更为复杂的约束。约束定义的约束更为复杂的约束。 与与CHECK CHECK 约束不同，触发器可以引用其它表中的列。约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的例如，触发器可以使用另一个表中的 SELECT SELECT 比较插入或更新的数据，比较插入或更新的数据，以及执行其它操作，如修改数据或显

4、示用户定义错误信息。以及执行其它操作，如修改数据或显示用户定义错误信息。实验实验 9 9 触发器触发器触发器的主要好处在于它们可以包含使用触发器的主要好处在于它们可以包含使用 Transact-SQL Transact-SQL 代码的复杂处理代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能逻辑。因此，触发器可以支持约束的所有功能（3 3）触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。）触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。（4 4）一个表中的多个同类触发器（）一个表中的多个同类触发器（INSERTINSERT、UPDATE UPDATE 或或 DELE

5、TEDELETE）允许采取）允许采取多个不同的对策以响应同一个修改语句。多个不同的对策以响应同一个修改语句。3 3触发器类型触发器类型UPDATEUPDATEINSERTINSERTDELETEDELETE4 4创建触发器的创建触发器的SQLSQL语句语句CREATE TRIGGER CREATE TRIGGER 触发器名触发器名 ON ON 表名表名 | | 视图名视图名 FOR |AFTER|INSTEAD OF INSERT ,UPDATE FOR |AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE ,DELETE ASAS IF UPDATE( IF UP

6、DATE( 列名列名 ) AND | OR ) AND | OR UPDATE( UPDATE( 列名列名 ) ) .n .n SQLSQL语句语句 .n .n 实验实验 9 9 触发器触发器 5 5修改触发器的修改触发器的SQLSQL语句语句ALTER TRIGGER ALTER TRIGGER 触发器名触发器名ON ON 表名表名 | | 视图名视图名 FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETE FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETE ASAS IF UPDATE(IF UPDATE(列名列名)AND|ORUP

7、DATE()AND|ORUPDATE(列名列名) .n .n SQL SQL语句语句 .n .n 6 6删除触发器的删除触发器的SQLSQL语句语句 DROP TRIGGER DROP TRIGGER 触发器名触发器名 ,.n ,.n 7 7触发器执行的时间触发器执行的时间（1 1）在）在UPDATEUPDATE、INSERTINSERT、DELETEDELETE语句执行后自动触发执行。语句执行后自动触发执行。（2 2）对）对“FOR|AFTER”FOR|AFTER”选项，触发器表中若定义了约束，则先处理约束，选项，触发器表中若定义了约束，则先处理约束，后执行触发器程序。后执行触发器程序。【实

8、验目的【实验目的】掌握触发器的创建、修改、删除及其使用方法。掌握触发器的创建、修改、删除及其使用方法。掌握触发器的功能。掌握触发器的功能。 实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 【实验目的【实验目的】掌握使用企业管理器创建触发器；掌握使用企业管理器创建触发器；掌握使用掌握使用SQLSQL创建触发器；创建触发器；掌握触发器的执行。掌握触发器的执行。【实验内容【实验内容】用企业管理器为表用企业管理器为表S S创建一级联更新触发器创建一级联更新触发器TRIGGER_STRIGGER_S。要求：若修改。要求：若修改S S表中一学生的学号，则表中一学生的学号，则SCSC表中与该学生

9、相关的学号自动修改。表中与该学生相关的学号自动修改。用企业管理器为表用企业管理器为表SCSC创建一限制更新触发器创建一限制更新触发器TRIGGER_SCTRIGGER_SC。要求：若修。要求：若修改改SCSC表中一记录的学号，则要检查表表中一记录的学号，则要检查表S S中是否存在与该学号相同的记录，若中是否存在与该学号相同的记录，若有则不许修改，若没有则可修改。有则不许修改，若没有则可修改。用用SQLSQL为表为表SCSC创建一触发器创建一触发器Score_sc_triScore_sc_tri。要求：当插入一个记录或修。要求：当插入一个记录或修改成绩时，确保此记录的成绩在改成绩时，确保此记录的

10、成绩在0 0100100之间。之间。用用SQLSQL为表为表C C创建一级联删除触发器创建一级联删除触发器TRIGGER_DCTRIGGER_DC：通过课程名从：通过课程名从C C表中删表中删除某课程信息，同时删除除某课程信息，同时删除SCSC表中与此课程相关的选课记录。表中与此课程相关的选课记录。实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 【实验步骤【实验步骤】 1 1用企业管理器为表用企业管理器为表S S创建一级联更新触发器创建一级联更新触发器TRIGGER_STRIGGER_S。（1 1）创建触发器。）创建触发器。 创建触发器命令 触发器属性窗口实验实验 9.1 9.1

11、创建和执行触发器创建和执行触发器 创建触发器创建触发器实验实验 9.1 9.1 创建和执行触发器创建和执行触发器（2 2）验证触发器的作用）验证触发器的作用 表S和表SC中的数据（a）数据表S（b）数据表SC触发器级联更新表S和表SC中的数据（a）数据表S（b）数据表SC实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 2 2企业管理器为表企业管理器为表SCSC创建一限制更新触发器创建一限制更新触发器TRIGGER_SCTRIGGER_SC 要求：若修改要求：若修改SCSC表中一记录的学号，则要检查表表中一记录的学号，则要检查表S S中是否存在与该学号相中是否存在与该学号相同的记录，

12、若有则不许修改，若没有则可修改。同的记录，若有则不许修改，若没有则可修改。（1 1）创建触发器）创建触发器 （2 2）验证触发器的作用）验证触发器的作用创建触发器创建触发器TRIGGER_SCTRIGGER_SC触发器级联更新表触发器级联更新表S S和表和表SCSC中的数据中的数据（a a）数据表）数据表S S（b b）数据表）数据表SCSC实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 3 3用用SQLSQL为表为表SCSC创建一触发器创建一触发器Score_sc_triScore_sc_tri 要求：当插入一个记录或修改成绩时，确保此记录的成绩在0100之间。（1 1）创建触发

13、器）创建触发器数据库数据库jxskjxsk的触发器的触发器对象对象创建触发器的创建触发器的SQLSQL实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 3 3用用SQLSQL为表为表SCSC创建一触发器创建一触发器Score_sc_triScore_sc_tri 要求：当插入一个记录或修改成绩时，确保此记录的成绩在0100之间。（2 2）验证触发器的作用）验证触发器的作用 验证触发器的验证触发器的SQLSQL操作操作操作后的表操作后的表SCSC数据数据表表SCSC的数据表的数据表实验实验 9.1 9.1 创建和执行触发器创建和执行触发器 4 4用用SQLSQL为表为表C C创建一个级

14、联删除触发器创建一个级联删除触发器TRIGGER_DCTRIGGER_DC：通过课程名从：通过课程名从C C表中删表中删除某课程信息，同时删除除某课程信息，同时删除SCSC表中与此课程相关的选课记录表中与此课程相关的选课记录（1 1）创建触发器）创建触发器 执行执行SQLSQL创建触发器创建触发器 数据库数据库jxskjxsk中的触发器中的触发器实验实验 9 9 触发器触发器 （2 2）验证触发器的作用。）验证触发器的作用。 为执行删除SQL时表C和SC数据窗口（a） 表C数据窗口（b） 表SC数据窗口对表C执行删除SQL实验实验 9 9 触发器触发器 （a）C表数据 删除记录后的C和SC表数

15、据（b）SC表数据实验实验 9.2 9.2 修改触发器修改触发器【实验目的【实验目的】 掌握使用企业管理器修改触发器；掌握使用企业管理器修改触发器； 掌握使用掌握使用SQLSQL修改触发器。修改触发器。【实验内容【实验内容】 用企业管理器修改触发器用企业管理器修改触发器TRIGGER_STRIGGER_S。要求：若删除表。要求：若删除表S S中中某一个学生的记录信息，某一个学生的记录信息， 则自动删除则自动删除SCSC表中与学生相关的记录信息。表中与学生相关的记录信息。 用用SQLSQL修改表修改表C C的触发器的触发器TRIGGER_DCTRIGGER_DC：通过课程名从：通过课程名从C C

16、表中表中删除某课程信息，同时删删除某课程信息，同时删 除除SCSC表和表和TCTC表中与此课程相关的记录。表中与此课程相关的记录。 实验实验 9.2 9.2 修改触发器修改触发器 【实验步骤【实验步骤】 1 1用企业管理器修改表用企业管理器修改表S S的触发器的触发器TRIGGER_STRIGGER_S 创建触发器命令 触发器属性窗口把文本框中的把文本框中的SQLSQL语句修改成下面的内容：语句修改成下面的内容：Create trigger TRIGGER_S Create trigger TRIGGER_S On S For DELETE On S For DELETE As declare

17、 SNO_DEL CHAR(2) As declare SNO_DEL CHAR(2)Select SNO_DEL=SNO from DELETEDSelect SNO_DEL=SNO from DELETEDDELETE from SC where SNO=SNO_DEL DELETE from SC where SNO=SNO_DEL 实验实验 9.2 9.2 修改触发器修改触发器 2 2用用SQLSQL修改表修改表C C的触发器的触发器TRIGGER_DCTRIGGER_DC： 要求：通过课程名从要求：通过课程名从C C表中删除某课程信息，同时删除表中删除某课程信息，同时删除SCSC表和

18、表和TCTC表中与表中与此课程相关的记录。此课程相关的记录。 在查询窗口中输入下面在查询窗口中输入下面SQLSQL语句：语句：USE jxskUSE jxskGOGOAlter trigger TRIGGER_DC Alter trigger TRIGGER_DC On SC For DELETE On SC For DELETE As declare CNO_DEL CHAR As declare CNO_DEL CHAR（2 2）Select CNO_DEL=CNO from DELETEDSelect CNO_DEL=CNO from DELETEDDELETE from SC WHER

19、E CNO=CNO_DEL DELETE from SC WHERE CNO=CNO_DEL DELETE from TC WHERE CNO=CNO_DEL DELETE from TC WHERE CNO=CNO_DEL GOGO实验实验 9.3 9.3 删除触发器删除触发器 【实验目的【实验目的】掌握使用企业管理器删除触发器；掌握使用企业管理器删除触发器；掌握使用掌握使用SQLSQL删除触发器。删除触发器。【实验内容【实验内容】用企业管理器删除表用企业管理器删除表S S的触发器的触发器TRIGGER_STRIGGER_S。；。；用用SQLSQL删除表删除表C C的触发器的触发器TRIGG

20、ER_DCTRIGGER_DC。【实验步骤【实验步骤】 1 1用企业管理器删除表用企业管理器删除表S S的的 触发器触发器TRIGGER_STRIGGER_S 删除触发器删除触发器TRIGGER_STRIGGER_S实验实验 9.3 9.3 删除触发器删除触发器 2 2用用SQLSQL删除表删除表C C的触发器的触发器TRIGGER_DCTRIGGER_DC。 在查询窗口中输入下面SQL语句：USE jxskUSE jxskDROP TRIGGER TRIGGER_DC DROP TRIGGER TRIGGER_DC GOGO实验实验 9 9 习题习题 【实验题报告【实验题报告】内容与要求内容与要求 基于实验六中的基于实验六中的“教学数据库教学数据库jxskjxsk”，创建下面触发器，并给出正反实，创建下面触发器，并给出正反实例：例：1.1.为表为表SCSC创建一触发器：当插入或修改一个记录时，确保此记录的成绩在创建一触发器：当插入或修改一个记录时，确保此记录的成绩在 0 0100100之间。之间。2.2.为教师表为教师表T T创建一触发器：男职工年龄不能超过创建一触发器：男职工年龄不能超过6060周岁，女职工职称是周岁，女职工职称是“教授教授”的年龄不超过的年龄不超过6060岁，其他女职工年