数据库|《数据库》第7章 SQL数据定义、更新及数据库编程（下）——存储过程和触发器 sql|数据库

知识点

7.6 存储过程
- 7.6.1 创建存储过程
- 7.6.2 执行存储过程
- 7.6.3 修改存储过程
- 7.6.4 删除存储过程
7.7 触发器
- 7.7.1 创建触发器
- 7.7.2 修改触发器
- 7.7.3 修改触发器

7.6 存储过程

存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合（函数）

该集合编译后存放在数据库中，可根据实际情况重新编译；

存储过程可直接在服务器端运行，也可在客户端远程调用运行，远程调用时存储过程还是在服务器端运行。

优点:
将业务操作封装
便于事务管理
实现一定程度的安全性保护
特别适合统计和查询操作
减少网络通信量
7.6.1 创建存储过程

语法： CREATE PROCEDURE [(<@parameterName> [= ] [OUTPUT] [, <@parameterName> [= ] [OUTPUT] ] ) ] AS

其中：
- ：存储过程的名称，必须符合标识符规则，且在同一个数据库中唯一；
- <@parameterName>：参数名，存储过程可不带参数，形式参数是变量，但实际参数可以是变量、常量和表达式；
- OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省时表示是输入参数。
如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。
- 临时表的表名前加一个#符号，如#myTemp
- 在存储过程尾部,使用语句：SELECT * FROM #myTemp将结果集合返回给调用者。
- 存储过程结束后，临时表自动被删除。
注意：
- 用户定义的存储过程只能在当前数据库中创建；
- 一个存储过程最大不能超过128MB。若超过128MB，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。

[例7.50] 输入某个同学的学号，统计该同学的平均分。 CREATE PROCEDURE proStudentByNo1(@sNo char(7)) AS SELECT a.studentNo, studentName, avg(score) FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a.studentNo=@sNo GROUP BY a.studentNo, studentName

[例7.51] 输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行显示该同学的姓名、选课名称和选课成绩。 CREATE PROCEDURE proStudentByNo2(@sNo char(7), @avg numeric(6, 2) OUTPUT) AS BEGIN DECLARE @sName varchar(20), @cName varchar(20) DECLARE @score tinyint, @sum int, @count tinyint SELECT @sum = 0, @count = 0 -- 定义、打开、获取游标 DECLARE curScore CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=@sNo AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo OPEN curScore FETCH curScore INTO @sName, @cName, @score WHILE (@@FETCH_STATUS = 0) BEGIN -- 业务处理 SELECT @sName, @cName, @score--逐行显示该同学的选课信息 SET @sum=@sum+@score SET @count=@count+1 FETCH curScore INTO @sName, @cName, @score END CLOSE curScore DEALLOCATE curScore IF @count = 0 SELECT @avg = 0 ELSE SELECT @avg=@sum/@count END

由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。
显示内容在调试存储过程时有作用，一旦存储过程调试正确，使用存储过程的修改命令将存储过程中的显示命令删除。

[例7.52] 输入某学院名称，统计该学院每个班级同学的选课信息, 返回班级编号、班级名称、课程名称、课程选课人数、课程平均分。 CREATE PROCEDURE proInstitute( @institute varchar(30) ) AS BEGIN DECLARE @className varchar(30), @courseName varchar(30) DECLARE @classNo char(6), @count tinyint, @avg numeric(5, 1) /* 创建一个临时表，存放每个班级的班级编号、班级名称、课程名称、课程选课人数、课程平均分 */ CREATE TABLE #myTemp ( classNochar(6), classNamevarchar(30), courseNamevarchar(30), classCounttinyint, classAvgnumeric(5, 1) ) -- 定义游标curClass，依据输入参数@institute，查找班级编号和班级名称 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=@institute OPEN curClass FETCH curClass INTO @classNo, @className WHILE (@@FETCH_STATUS = 0) BEGIN -- 定义游标curCourse，查找@classNo班选课的课程名称、选课人数、平均分 DECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=@classNo GROUP BY courseName OPEN curCourse FETCH curCourse INTO @courseName, @count, @avg WHILE (@@FETCH_STATUS = 0) BEGIN /* 将班级编号、班级名称、课程名称、课程选课人数、课程平均分插入到临时表#myTemp中 */ INSERT INTO #myTemp VALUES( @classNo, @className, @courseName, @count, @avg ) -- 获取当前游标值，取该班级下一门课程的课程名称、选课人数和平均分 FETCH curCourse INTO @courseName, @count, @avg END CLOSE curCourse DEALLOCATE curCourse -- 获取游标curClass的当前游标值，即取下一个班级 FETCH curClass INTO @classNo, @className END CLOSE curClass DEALLOCATE curClass --显示临时表的内容，同时将临时表的内容返回给调用者 SELECT * FROM #myTemp END

本例使用嵌套游标，读者通过该例掌握嵌套游标的使用方法。
分析：

本例涉及两个参数
- 一个是输入参数：学院名称，设为@institute；
- 一个是输出参数，它为一个集合值，包含了该学院所有班级的班级编号、班级名称、课程名称、课程选课人数、课程平均分；
- 对于集合值输出参数，在存储过程中定义一个临时表来存储该集合，设临时表为#myTemp
- 在存储过程尾部使用语句“SELECT * FROM #myTemp”将该集合返回给调用者。
声明5个临时变量，分别保存查询出来的班级编号@classNo、班级名称@className、课程名称@courseName、选课人数@count、选课平均分@avg。
由于一个学院有多个班级，定义一个游标curClass，根据输入的学院名称，查询该学院所有的班级编号和班级名称。

将查询出的班级编号和班级名称放入变量@classNo、@className中。` 定义游标语句为： DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=@institute

由于一个班级选修了多门课程，需依据查询出来的班级号@classNo，按选课的课程名进行分组计算，统计该班级每门课程的选课人数和选课平均分。

需要使用第二个游标，将查询出来的该班级的选课人数和平均分放入变量@count和@avg中。定义游标语句为： DECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=@classNo GROUP BY courseName 注意：@classNo变量的值是从外游标中获取的班级编号。

将查询出来的班级编号、班级名称、课程名称、课程选课人数、课程平均分插入到临时表#myTemp中。
在本例中，获取班级编号、班级名称不能写成：

SELECT @classNo=classNo, @className=className FROM Class WHERE institute=@institute

因为：
一个学院有多个班级，该查询返回一个元组集合。
变量@classNo和@className仅分别接收一个数据。必须使用游标，本例定义游标为curClass。
7.6.2 执行存储过程

使用存储过程时，必须执行命令EXECUTE 语法： EXECUTE [ [<@parameterName> =] , [<@parameterName> =] <@variableName> [OUTPUT], [<@parameterName> =] , [<@parameterName> =] <@variableName> [OUTPUT] ] ] 注意：EXECUTE的参数必须与对应的PROCEDURE的参数相匹配。

[例7.53] 执行存储过程proStudentByNo1 EXECUTE proStudentByNo1 '1600001' [例7.54] 执行存储过程proStudentByNo2 DECLARE @avg numeric(5, 1) EXECUTE proStudentByNo2 '1600001', @avg OUTPUT SELECT @avg [例7.55] 执行存储过程proInstitute EXECUTE proInstitute '信息管理学院' 也可以使用命令： DECLARE @institute varchar(30) SET @institute='信息管理学院' EXECUTE proInstitute @institute

7.6.3 修改存储过程

语法为： ALTER PROCEDURE [ <@parameterName> [= ] [OUTPUT] [, <@parameterName> [= ] [OUTPUT] ] ] AS 注意：由于存储过程是在服务器端执行，程序中不需要有输出命令SELECT，由SELECT引出的输出不会在客户端出现。

7.6.4 删除存储过程

删除存储过程语法： DROP PROCEDURE

7.7 触发器

触发器(trigger)是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。
触发器是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次，事务可用于触发器中。
事务定义：
BEGIN TRANSACTION [ ]
COMMIT TRANSACTION [ ]
ROLLBACK TRANSACTION [ ]
下面以SQL Server为例介绍触发器。注意：
- deleted表、inserted表的结构与触发器作用的基本表结构完全一致;
- 当针对触发器作用的基本表(简称作用表)的SQL语句开始执行时，自动产生deleted表、inserted表的结构与内容；
- 当SQL语句执行完毕， deleted表、inserted表也随即被删除。
deleted表
存储当DELETE和UPDATE语句执行时所影响的行的拷贝，即在DELETE和UPDATE语句执行前，先将该语句所作用的行转移到deleted表中，即将被删除的元组或修改前的元组值存入deleted表中。
inserted表
存储当INSERT和UPDATE语句执行时所影响的行的拷贝，即在INSERT和UPDATE语句执行期间，新行被同时加到inserted表和触发器作用的表中。即将被插入的元组或修改后的元组值存入inserted表中，同时更新触发器作用的基本表。
实际上，UPDATE命令是删除后紧跟着插入，旧行首先拷贝到deleted表中，新行同时拷贝到inserted表和基本表中。
触发器仅在当前数据库中被创建
- 触发器有3种类型，即插入、删除和修改；
- 插入、删除或修改也可组合起来作为一种类型的触发器；
- 查询操作不会产生触发动作，没有查询触发器类型。

7.7.1 创建触发器

创建触发器的语法： CREATE TRIGGER ON FOR { INSERT | UPDATE | DELETE } AS -- 触发动作的执行体，即触发器代码

其中：
：触发器的名称，在1个数据库中必须唯一；
：触发器作用的基本表，该表也称为触发器的目标表；
{ INSERT | UPDATE | DELETE }：触发器事件，触发器的事件可以是插入INSERT、修改UPDATE或删除DELETE事件，也可以是这几个事件的组合。
- INSERT 类型的触发器是指：当对指定基本表执行了插入操作时系统自动执行触发器代码。
- UPDATE 类型的触发器是指：当对指定基本表执行了修改操作时系统自动执行触发器代码。
- DELETE类型的触发器是指：当对指定基本表执行了删除操作时系统自动执行触发器代码。
：触发动作的执行体，即触发器代码
- 如果该触发器代码执行失败，则激活触发器的事件就会终止，且触发器的目标表及触发器可能影响的其它表不发生任何变化，即执行事务的回滚操作。

[例7.58] 创建触发器，保证学生表中的性别仅能取男或女。分析：本例需要使用插入和修改两个类型的触发器，因为可能破坏约束“性别仅能取男或女”的操作是插入和修改操作。违约条件是：如果在inserted表中存在有性别取值不为“男”或“女”的记录(由于inserted表保存了修改后的记录，只要对inserted表进行判断即可)，则取消本次操作——取消本次的所有操作。插入类型的触发器 CREATE TRIGGER sexIns-- 创建插入类型的触发器 ON Student-- 触发器作用的基本表 FOR INSERT-- 触发器的类型，即触发该触发器被自动执行的事件 AS IF EXISTS (SELECT * FROM inserted WHERE sex NOT IN ('男', '女')) ROLLBACK-- 事务的回滚操作，即终止触发该触发器的插入操作修改类型的触发器 CREATE TRIGGER sexUpt-- 创建修改类型的触发器 ON Student FOR UPDATE AS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ('男', '女') ) ROLLBACK-- 事务的回滚操作，即终止触发该触发器的修改操作该例也可以合并为一个触发器： CREATE TRIGGER sexUptIns ON Student FOR INSERT, UPDATE AS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ('男', '女') ) ROLLBACK 本例的inserted表结构与Student表结构相同。

[例7.59] 创建触发器，如果对学生表进行了更新(插入、删除和修改)操作，则自动修改班级表中的班级人数。假设一次仅允许更新一个学生记录，否则当作违反约束规则。 /* 创建插入类型的触发器，inserted表结构与Student表结构相同 */ CREATE TRIGGER ClassIns ON Student FOR INSERT AS BEGIN /* 定义一个变量@classNo，用于接受所插入的学生所属的班级编号 */ DECLARE @classNo char(6) /* 如果插入的记录数大于1条, 则回滚 */ IF ( SELECT count(*) FROM inserted ) > 1 ROLLBACK ELSE BEGIN /* 找出插入的学生所属的班级编号放到变量@classNo中 */ SELECT @classNo=classNo FROM inserted /* 修改班级表中对应班级编号为@classNo的班级人数 */ UPDATE Class SET classNum=classNum+1 WHERE classNo=@classNo END END/* 创建删除类型的触发器，deleted表结构与Student表结构相同 */ CREATE TRIGGER ClassDel ON Student FOR DELETE AS BEGIN /* 定义一个变量@classNo，用于接受所删除的学生所属的班级编号 */ DECLARE @classNo char(6) /* 如果删除的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted ) > 1 ROLLBACK ELSE BEGIN /* 找出删除的学生所属的班级编号放到变量@classNo中 */ SELECT @classNo=classNo FROM deleted /* 修改班级表中对应班级编号为@classNo的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=@classNo END END/* 创建修改类型的触发器，deleted和inserted表结构与Student表结构相同 */ CREATE TRIGGER ClassUpt ON Student FOR UPDATE AS BEGIN /* 定义一个变量@oldClassNo，用于接受所修改前的学生所属的班级编号 */ /* 定义一个变量@newClassNo，用于接受所修改后的学生所属的班级编号 */ DECLARE @oldClassNo char(6), @newClassNo char(6) /* 如果修改的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted ) > 1 ROLLBACK ELSE BEGIN /* 找出修改前的学生所属的班级编号放到变量@oldClassNo中 */ SELECT @oldClassNo=classNo FROM deleted /* 找出修改后的学生所属的班级编号放到变量@newClassNo中 */ SELECT @newClassNo=classNo FROM inserted /* 修改班级表中对应班级编号的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=@oldClassNo UPDATE Class SET classNum=classNum+1 WHERE classNo=@newClassNo END END

分析：

该触发器的含义是：
- 当对学生表Student删除和插入记录时必须修改班级人数；
- 当修改学生表中某同学的所属班级时，也要修改班级表中的相应班级的班级人数；
- 分别为插入、删除和修改操作设计触发器。
由于规定一次仅能修改一个学生记录，因此在触发器中必须进行判断：如果执行DML语句作用的对象超过一条记录，则取消本次操作。
由于规定一次仅能修改一个学生记录，因此可直接在SELECT语句中使用变量接收查询出来的属性值，不需要使用游标：SELECT @classNo=classNo FROM inserted
本例在修改类型的触发器中要同时使用deleted表和inserted表。
可以增加一条IF语句，判断学生表中的classNo是否被修改了？如果修改了才需要去修改班级表中的classNum。
如果一次允许插入多个学生记录，则实现自动修改班级表中班级人数的插入类型的触发器如下页所示，请读者写出相应的删除触发器和修改触发器。

/* 创建插入类型的触发器，inserted表结构与Student表结构相同 */ CREATE TRIGGER ClassInsMany-- 一次插入多条学生记录的插入类型的触发器 ON Student FOR INSERT AS-- 使用游标来实现 BEGIN /* 定义一个变量@classNo，用于接受所插入的学生所属的班级编号 */ DECLARE @classNo char(6) DECLARE curStudent CURSOR FOR--定义游标对多个插入的学生进行逐个处理 SELECT classNo FROM inserted OPEN curStudent FETCH curStudent INTO @classNo WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE Class SET classNum=classNum+1 WHERE classNo=@classNo--修改班级表中班级编号为@classNo的班级人数 FETCH curStudent INTO @classNo END CLOSE curStudent DEALLOCATE curStudent END

也可不使用游标，直接通过一条SQL语句完成班级人数的修改，插入类型的触发器如下：

/* 创建插入类型的触发器，inserted表结构与Student表结构相同 */ CREATE TRIGGER ClassInsMany1-- 一次插入多条学生记录的插入类型的触发器 ON Student FOR INSERT AS-- 不使用游标来实现 BEGIN UPDATE Class Cla SET Cla.classNum=Cla.classNum+InsCnt.cnt FROM ( SELECT Ins.classNo, count(*) cnt--统计每一个班级插入学生的人数cnt FROM inserted Ins GROUP BY Ins.classNo ) InsCnt WHERE InsCnt.classNo=Cla.classNo END

触发器常用于保证完整性，并在一定程度上实现安全性，如可以用触发器来进行审计。

[例7.60] 创建触发器，只有数据库拥有者(dbo)才可以修改成绩表中的成绩，其它用户对成绩表的插入、删除操作必须记录下来。记录用户的操作轨迹，首先创建一张审计表，其结构如下： CREATE TABLE TraceEmployee ( useridchar(10)NOT NULL,--用户标识 numberintNOT NULL,--操作次数 operateDatedatetimeNOT NULL,--操作时间 operateTypechar(10)NOT NULL,--操作类型：插入/删除/修改 studentNochar(7)NOT NULL, courseNochar(3)NOT NULL, termNochar(3)NOT NULL, scorenumeric(5,1)NOT NULL, CONSTRAINT TraceEmployeePK PRIMARY KEY (userid, number) )分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。插入类型的触发器 CREATE TRIGGER ScoreTracIns-- 创建插入类型的触发器 ON Score FOR INSERT AS BEGIN DECLARE @studNo char(7), @courNo char(3), @termNo char(3) DECLARE @score numeric(5, 1), @num int IF user<>'dbo' AND EXISTS ( SELECT * FROM inserted ) --非dbo用户对成绩表进行插入操作 BEGIN SELECT @num=max(number)--获取该用户以前的操作次数 FROM TraceEmployee WHERE userid =user IF @num IS NULL SELECT @num = 0分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。 DECLARE curTrance CURSOR FOR SELECT * FROM inserted-- inserted表结构同触发器作用的Score表 OPEN curTrance FETCH curTrance INTO @studNo, @courNo, @termNo, @score WHILE (@@FETCH_STATUS = 0) BEGIN--通过游标记录非dbo用户对成绩表进行的所有插入操作 SET @num=@num+1--该用户的操作次数自动加1 INSERT INTO TraceEmployee VALUES--在审计表中添加插入操作轨迹 ( user, @num, getdate(), 'insert', @studNo, @courNo, @termNo, @score ) FETCH curTrance INTO @studNo, @courNo, @termNo, @score END CLOSE curTrance DEALLOCATE curTrance END END分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。删除类型的触发器 CREATE TRIGGER ScoreTracDel-- 创建删除类型的触发器 ON Score FOR DELETE AS BEGIN DECLARE @studNo char(7), @courNo char(3), @termNo char(3) DECLARE @score numeric(5, 1), @num int IF user<>'dbo' AND EXISTS ( SELECT * FROM deleted ) --非dbo用户对成绩表进行删除操作 BEGIN SELECT @num=max(number)--获取该用户以前的操作次数 FROM TraceEmployee WHERE userid = user IF @num IS NULL SELECT @num = 0分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。 DECLARE curTrance CURSOR FOR SELECT * FROM deleted-- deleted表结构同触发器作用的Score表 OPEN curTrance FETCH curTrance INTO @studNo, @courNo, @termNo, @score WHILE (@@FETCH_STATUS = 0) BEGIN--通过游标记录非dbo用户对成绩表进行的所有删除操作 SET @num=@num+1--该用户的操作次数自动加1 INSERT INTO TraceEmployee VALUES--在审计表中添加删除操作轨迹 ( user, @num, getdate(), 'delete', @studNo, @courNo, @termNo, @score ) FETCH curTrance INTO @studNo, @courNo, @termNo, @score END CLOSE curTrance DEALLOCATE curTrance END END分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。修改类型的触发器 CREATE TRIGGER ScoreTracUpt-- 创建修改类型的触发器 ON Score FOR UPDATE AS IF user<>'dbo' AND EXISTS ( SELECT * FROM deleted ) --非dbo用户不允许对成绩表进行修改操作 ROLLBACK -- user常量是SQL Server中当前登陆用户的用户标识。

注意：

原则上并不限制一张基本表上创建的触发器的数量。
由于触发器是自动执行的，为一张基本表建立了多个触发器，必然加大系统的开销。
如果触发器设计得不好，会带来不可预知的后果。
触发器常常用于维护复杂的完整性约束，不用于业务处理。
- 用户的业务处理常常使用存储过程实现。
凡是可以用一般约束限制的，就不要使用触发器。
- 如限制性别仅取男或女，可以使用检查约束CHECK实现。
一张表可以有多个触发器，且同一类型触发器也可以有多个
- 有的DBMS按照触发器建立的时间顺序进行触发。
- 有的DBMS按照触发器名字顺序进行触发。

7.7.2 修改触发器

语法为： ALTER TRIGGER ON FOR { INSERT | UPDATE | DELETE } AS

[例7.61] 修改例7.60中的修改类型的触发器，允许非dbo用户修改Score表的成绩数据，但是必须将修改操作的轨迹记录在审计表TraceEmployee中ALTER TRIGGER ScoreTracUpt ON Score FOR UPDATE AS BEGIN /* 声明两个变量@oldScore和@newScore，分别接受修改前、后的成绩 */ DECLARE @oldScore numeric(5, 1), @newScore numeric(5, 1) DECLARE @studNo char(7), @courNo char(3), @termNo char(3), @num int IF user<>'dbo'--非dbo用户对Score表进行操作 BEGIN IF update(studentNo) OR update(courseNo) OR update(termNo) ROLLBACK--如果修改了学号、课程号或学期号属性，则回滚 ELSE IF UPDATE(score)-- 表示对成绩进行了修改 BEGIN SELECT @num=max(number)--获取该用户以前的操作次数 FROM TraceEmployee WHERE userid = user IF @num IS NULL SELECT @num = 0 -- 定义游标uptCur，查找某学号、课程号和学期号选课记录的修改前、后的成绩 DECLARE uptCur CURSOR FOR SELECT a.score, b.score, a.studentNo, a.courseNo, a.termNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo AND a.courseNo=b.courseNo AND a.termNo=b.termNo OPEN uptCur--打开游标 FETCH uptCur INTO @newScore, @oldScore, @studNo, @courNo, @termNo WHILE ( @@FETCH_STATUS = 0 ) BEGIN--对每条成绩修改记录，在审计表中添加2条记录反映修改前、后的成绩 SET @num=@num+1--该用户的操作次数自动加1 INSERT INTO TraceEmployee VALUES (user, @num, getdate(), 'oldUpt', @studNo, @courNo, @termNo, @oldScore) SET @num=@num+1--该用户的操作次数自动加1 INSERT INTO TraceEmployee VALUES (user, @num, getdate(), 'newUpt', @studNo, @courNo, @termNo, @newScore) FETCH uptCur INTO @newScore, @oldScore, @studNo, @courNo, @termNo END CLOSE uptCur--关闭游标 DEALLOCATE uptCur--释放游标 END END END OPEN uptCur--打开游标 FETCH uptCur INTO @newClassNo, @oldClassNo--获取当前游标值 WHILE ( @@FETCH_STATUS=0 ) BEGIN /* 更新班级表中对应班级编号的班级人数 */ UPDATE Class SET classNum=classNum-1-- 原班级人数减1 WHERE classNo=@oldClassNo UPDATE Class SET classNum=classNum+1-- 现班级人数加1 WHERE classNo=@newClassNo FETCH uptCur INTO @newClassNo, @oldClassNo--获取下一个游标值 END CLOSE uptCur--关闭游标 DEALLOCATE uptCur--释放游标 END

【数据库|《数据库》第7章 SQL数据定义、更新及数据库编程（下）——存储过程和触发器】分析：

本例中有两个触发条件：首先，如果修改了学号、课程号、学期号，则拒绝修改，即执行回滚操作；然后，如果只修改了成绩，需要将修改成绩操作的轨迹记录在审计表TraceEmployee中。使用update(attribute)函数可判断是否对属性attribute进行了修改。
本例允许对多条记录的成绩进行修改，需要使用游标，找出修改前后的成绩，分别放入到变量@oldScore和@newScore中。
inserted表中保存了修改后的成绩，deleted表中保存了修改前的成绩，因此要找出修改前、后的成绩，可以对inserted表和deleted表进行连接操作，连接条件是学号、课程号、学期号相等，其连接语句如下：

SELECT a.score, b.score, a.studentNo, a.courseNo, a.termNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo AND a.courseNo=b.courseNo AND a.termNo=b.termNo

7.7.3 修改触发器