第九章 存储过程与触发器2014-11-28 19.39.57.ppt

@MINCHENGJI tinyint OUTPUT AS SELECT @AVGCHENGJI=AVG(成绩),@MAXCHENGJI=MAX(成绩),@MINCHENGJI=MIN(成绩) FROM 课程注册 WHERE 课程号in(SELECT 课程号FROM 课程WHERE 课程名=@KECHENGMING) GO USE student --声明四个变量，用于保存输入和输出参数 DECLARE @KECHENGMING varchar(20) DECLARE @AVGCHENGJI1 tinyint DECLARE @MAXCHENGJI1 tinyint DECLARE @MINCHENGJI1 tinyint --为输出参数赋值 SELECT @KECHENGMING=计算机基础 --执行存储过程 EXEC 单科成绩分析@KECHENGMING, @AVGCHENGJI1 OUTPUT, @MAXCHENGJI1 OUTPUT, @MINCHENGJI1 OUTPUT --显示结果 SELECT @KECHENGMING AS 课程名,@AVGCHENGJI1 AS 平均成绩,@MAXCHENGJI1 AS最高成绩,@MINCHENGJI1 AS 最低成绩 GO 9.2 触发器 9.2.1 触发器的概念 触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成，可以完成存储过程能完成的功能，但是它具有自己的显著特点：它与表紧密相连，可以看做表定义的一部分；它不可能通过名称被直接调用，更不允许参数，而是当用户对表中的数据进行修改时，自动执行；它可以用于SQL Server约束、默认值和规则的完整性检查、实施更为复杂的数据完整性约束。 9.2.2 触发器的优点 触发器包含复杂的处理逻辑，能够实现复杂的完整性约束。同其他约束相比，它主要有以下优点： （1）触发器自动执行。在对表中的数据做了任何修改（如手工输入或者通过应用程序实现的修改）之后立即被激活。 （2）触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，可以在“产品”表的“产品编号”字段上建立一个插入触发器，当对“产品”表增加记录 时，在“产品销售”表的“产品编号”上自动插入“产品编号”值。 （3）触发器可以实现比CHECK约束更为复杂的数据完整性约束。在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。例如，在student数据库中，向“学生”表中插入记录时，当输入“系部代码”时，必须先检查“系部”表中是否存在该系。这只能通过触发器实现，而不能通过CHECK约束完成。 （4）触发器可以评估数据修改前后的表的状态，并根据其差异采取对策。 （5）一个表中可以同时存在三个不同操作的触发器（INSERT、UPDATE或DELETE），对于同一个修改语句可以有多个不同的响应对策。 9.2.3 触发器的种类 在SQL Server 2005中，按触发被激活的时机可以将触发器分为两种类型：AFTER触发器和INSTEAD OF触发器。 （1）AFTER触发器又称为后触发器，该类触发器是在引起触发器执行的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或语法错误）而执行失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可为每个触发器操作（INSERT、UPDATE或DELETE）创建多个AFTER触发器。如果表有多个AFTER触发器，可使用sp_settriggerorder定义哪个AFTER触发器最先激发，哪个最后激发。除第一个和最后一个触发器外，所有其他的AFTER触发器的激发顺序不确定，并且无法控制。 （2）INSTEAD OF触发器又称为替代触发器，当引起触发器执行的修改语句停止时，该类触发器替代触发操作执行。该类触发器既可在表上定义，也可在视图上定义。对于每个该类触发操作（INSERT、UPDATE或DELETE），只能定义一个INSTEAD OF触发器。 9.2.4 触发器的创建和执行 1．创建触发器 触发器可以在对象资源管理器中创建，也可以在查询分析器中用SQL语句创建。在创建该类触发器前，必须注意以下六点： ● CREATE TRIGGER语句必须是批处理中的第一条语句。将该批处理中随后的其他所有语句解释为CREATE TRIGGER语句定义的一部分。 ● 只能在当前数据库中创建触发器，触发器名称必须遵循标识