第 9章 触发器
9.1 触发器的概念
9.2 触发器的种类与工作原理
9.3 创建触发器
9.4 查看、修改触发器
9.5 删除触发器
9.1 触发器的概念
SQL Server中除可以使用约束和规则强制实施数据完整性外，还可以使用触发器实现数据的完整性。
触发器 （ TRIGGER） 是一种实施数据完整性的特殊类型存储过程,它不同于前面介绍过的存储过程,兼有强制参照完整性和存储过程的双重特性 。 在对表执行 UPDATE,INSERT,DELETE命令时,SQL Server就会自动触发执行触发器所定义的 SQL语句,从而确保对数据的处理必须符合由这些 SQL语句所定义的规则,
这相当于执行了一个存储过程 。 但触发器与一般的存储过程又有不同,存储过程是数据库对象,一般由用户直接调用执行 （ 除自动执行存储过程 ） ；而触发器依赖于数据表,只能通过操作 UPDATE,INSERT、
DELETE等命令触发,用户不能直接调用执行 。
第
9
章触发器
< >
9.1 触发器的概念触发器的主要功能是能够实现由主键与外键所不能保证的复杂的参照完整性和数据的一致性 。 此外,还有以下功能：
（ 1） 强化约束 。 触发器能够实现比 CHECK 语句更为复杂的约束 。
（ 2） 跟踪执行 。 触发器可以侦测数据库中内在的操作,从而不允许未经许可的插入,更新和删除操作影响数据库 。
（ 3） 级联运行 。 触发器可以侦测数据库内的操作,并自动地级联影响整个数据库中的各项内容 。
（ 4） 存储过程的调用 。 为了响应数据库更新,触发器可以调用一个或多个存储过程 。
因此,触发器可以解决高级形式的业务规则或复杂的行为限制以及实现定制记录等方面的问题 。 触发器能够根据某表数据修改前后状态所发生的差异,执行相应的处理 。 同时一个表的同一类型 （ INSERT,UPDATE,DELETE） 的多个触发器能够对同一种数据操作采取多种不同的处理 。
第
9
章触发器
< >
9.2 触发器的种类与工作原理
9.2.1 触发器的种类
SQL Server 2000支持两种类型的触发器,AFTER触发器和
INSTEAD OF触发器 。
l AFTER 触发器
AFTER 触发器即为 SQL Server 2000版本以前所介绍的触发器 。 该类型触发器要求只在执行了某一操作 （ INSERT,UPDATE、
DELETE） 之后,触发器才被触发,且只能在表上定义 。 可以为针对表的同一操作定义多个触发器 。 对于 AFTER触发器,可以定义哪一个触发器被最先触发,哪一个被最后触发,通常使用系统过程 sp_settriggerorder 来完成此任务 。
l INSTEAD OF触发器
INSTEAD OF触发器表示并不执行命令所定义的操作 （ INSERT、
UPDATE,DELETE）,而仅是执行触发器中定义的代码 。 它既可在表上定义,也可以在视图上定义,但对同一操作只能定义一个 INSTEAD OF触发器 。
第
9
章触发器
< >
9.2 触发器的种类与工作原理
9.2.2 触发器的工作原理当向表执行 INSERT,UPDATE或 DELETE语句时,若该表设置了触发器,则 SQL Server将根据不同的操作自动生成一个或二个临时表,Inserted表或 Deleted表,
同 时 将 操 作 数 据 送 入 触 发 器 表,Inserted表或
Deleted表中 。
Inserted表和 Deleted表是 SQL Server为触发器语句创建的逻辑表,存储在内存中,不是存储在数据库中,因此不允许用户直接对其修改 。 它们的结构与定义触发器相关表的结构相同,用于保存用户操作可能改变的行的新值或旧值,由系统自动维护 。 当触发器工作完成后,与之相关的逻辑表将自动删除 。
第
9
章触发器
< >
9.3 创建触发器触发器既可以使用 T-SQL语句创建,也可以在企业管理器中创建 。
创建触发器时,需要确定触发器的以下三个组成部分：
l 确定在其上定义触发器的表；
l 触发器将何时激活 。 指明激活触发器的数据修改语句,有效选项为 INSERT,UPDATE 或 DELETE。
多个数据修改语句可激活同一个触发器；
l 执行触发操作所执行的 T-SQL语句 。
第
9
章触发器
< >
9.3 创建触发器
9.3.1 使用 T-SQL语句创建触发器创建触发器的 CREATE TRIGGER语句的语法格式如下：
CREATETRIGGERtrigger_name
ONtable_name| view_name
[ WITHENCRYPTION]
{ { { FOR | AFTER | INSTEAD OF } { [INSERT][,]
[DELETE][,][UPDATE]}
[ NOTFORREPLICATION]
AS
[ { IFUPDATE( column)
[ { AND | OR} UPDATE( column) ] [,..n]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator }
updated_bitmask)
{ comparison_operator} column_bitmask[,..n] } ]
sql_statement[,..n]
}}
第
9
章触发器
< >
9.3 创建触发器
1,创建 INSERT触发器
【 例 9-1】 为成绩表 （ Grade） 创建一个 INSERT触发器,用于自动计算学生已修的学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Insert')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Insert
GO
CREATE TRIGGER TRGrade_Insert /* 触发器名 */
ON Grade /* 作用的表 */
FOR INSERT /* 创建 INSERT触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @CreditHour TINYINT /* 学分 */
DECLARE @Grade TINYINT /* 成绩 */
SELECT @Grade=G.Grade,@CreditHour=C.CreditHour
FROM Grade G INNER JOIN Inserted I
ON G.StudentID = I.StudentID and G.CourseID = I.CourseID
INNER JOIN Course C /* 按 CourseID联接 Course */
ON C.CourseID = I.CourseID
IF (@Grade>=60) /* 成绩大于等于 60 */
BEGIN
UPDATE Student/* 在已修学分中加上插入课程的学分 */
SET CreditHour=CreditHour + @CreditHour
FROM Student S,Inserted I
WHERE S.StudentID = I.StudentID
END
GO
第
9
章触发器
< >
9.3 创建触发器
2,创建 DELETE触发器
【 例 9-2】 为成绩表 （ Grade） 创建一个 DELETE触发器,用于自动计算学生已修的学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Delete')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Delete
GO
CREATE TRIGGER TRGrade_Delete /* 触发器名 */
ON Grade /* 作用的表 */
FOR DELETE /* 创建 DELETE触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @CreditHour TINYINT /* 学分 */
DECLARE @Grade TINYINT /* 成绩 */
DECLARE @StuID VARCHAR(10)
DECLARE @CourseID VARCHAR(8)
DECLARE @Gradeo TINYINT
SELECT * FROM Deleted /* 查看删除的数据,调试成功后该行需删除 */
SELECT @Grade=D.Grade,@CreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@Grade>=60) BEGIN /* 成绩大于等于 60 */
UPDATE Student/* 已修学分累计 */
SET CreditHour=CreditHour - @CreditHour
FROM Student S,Deleted D
WHERE S.StudentID = D.StudentID
END
GO
第
9
章触发器
< >
9.3 创建触发器
3,创建 UPDATE触发器
【 例 9-3】 为成绩表 （ Grade） 创建一个 UPDATE触发器,用于自动计算相关学生的已修学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Update')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Update
GO
CREATE TRIGGER TRGrade_Update /* 触发器名 */
ON Grade /* 作用的表 */
FOR UPDATE /* 创建 UPDATE触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @OldStuID VARCHAR(10),@OldCourseID VARCHAR(8)
/* 修改前的旧数据 */
DECLARE @OldGrade TINYINT,@OldCreditHour TINYINT
DECLARE @NewStuID VARCHAR(10),@NewCourseID VARCHAR(8)
/* 修改后的新数据 */
DECLARE @NewGrade TINYINT,@NewCreditHour TINYINT
-- 取修改后的数据
SELECT @NewStuID=G.StudentID,@NewCourseID=G.CourseID,
@NewGrade=G.Grade,@NewCreditHour=C.CreditHour
FROM Grade G INNER JOIN Inserted I
ON G.StudentID = I.StudentID
and G.CourseID = I.CourseID INNER JOIN Course C /* 按
CourseID联接 Course */
ON I.CourseID = C.CourseID
第
9
章触发器
< >
9.3 创建触发器
-- 取修改前的数据
SELECT @OldStuID=D.StudentID,@OldCourseID=D.CourseID,
@OldGrade=D.Grade,@OldCreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@NewStuID=@OldStuID) /* 同一个学生 */
BEGIN
IF (@NewGrade>=60 AND @OldGrade>=60) /* 新成绩 >=60而旧成绩 <=60*/
UPDATE Student /* 从已修学分中上加上新课程的学分,
再减去旧课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour -
@OldCreditHour
WHERE StudentID = @NewStuID
ELSE
IF (@NewGrade>=60 AND @OldGrade<60) /* 新成绩 >=60而旧成绩 <60*/
UPDATE Student /* 从已修学分中加上新课程的学分 */
第
9
章触发器
< >
9.3 创建触发器
-- 取修改前的数据
SELECT @OldStuID=D.StudentID,@OldCourseID=D.CourseID,
@OldGrade=D.Grade,@OldCreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@NewStuID=@OldStuID) /* 同一个学生 */
BEGIN
IF (@NewGrade>=60 AND @OldGrade>=60) /* 新成绩 >=60而旧成绩 <=60*/
UPDATE Student /* 从已修学分中上加上新课程的学分,
再减去旧课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour -
@OldCreditHour
WHERE StudentID = @NewStuID
ELSE
第
9
章触发器
< >
9.3 创建触发器
IF (@NewGrade>=60 AND @OldGrade<60) /* 新成绩 >=60而旧成绩 <60*/
UPDATE Student /* 从已修学分中加上新课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour
WHERE StudentID = @NewStuID
ELSE
IF (@NewGrade<60 AND @OldGrade>=60) /* 新成绩 <60而旧成绩 >=60*/
UPDATE Student /* 从已修学分中减去旧课程的学分 */
SET CreditHour=CreditHour - @OldCreditHour
WHERE StudentID = @NewStuID
END
ELSE /* 不同的学生 */
第
9
章触发器
< >
9.3 创建触发器
BEGIN
IF (@NewGrade>=60) /* 成绩大于等于 60 */
UPDATE Student /* 从已修学分中加上修改后课程的学分
*/
SET CreditHour=CreditHour + @NewCreditHour
FROM Student
WHERE StudentID = @NewStuID
IF (@OldGrade>=60) /* 成绩大于等于 60 */
UPDATE Student /* 从已修学分中减去修改前课程的学分
*/
SET CreditHour=CreditHour - @OldCreditHour
FROM Student
WHERE StudentID = @OldStuID
END
GO
第
9
章触发器
< >
9.3 创建触发器
9.3.2 使用企业管理器创建触发器通过企业管理器,可以方便地创建触发器,具体步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中创建触发器的数据库
（ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要创建触发器的表 。
4） 右击要在其上创建触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,命令,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择,新建,,系统 在,文本,文本框中显示了 CREATE TRIGGER语句的框架 。 在此修改触发器的名称,触发参数以及对应的 SQL语句,将例 9-4中的触发器代码输入 。
6） 单击,检查语法,按钮,检查触发器中语句的合法性,直到,语法检查成功,。
7） 单击,应用,→,确定,按钮,就可完成触发器的创建工作 。 此时,下位,名称,列 表 框,列 表 中 多 了 一 个 名 为
TRSpeciality_Course_Insert的 INSERT触发器,其前导图标为,”,说明创建 INSERT触发器成功 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.1 查看触发器
1,查看触发器的类型信息通过系统存储过程 sp_helptrigger查看指定表中定义的触发器类型信息 。
其语法格式为：
sp_helptrigger [ @tabname = ] 'table'
[,[ @triggertype = ] 'type' ]
2,查看触发器的定义文本通过系统存储过程 sp_helptext查看未加密触发器的定义文本 。 例如：
执行 sp_helptext 'TRSpeciality_Course_Insert',就 可 显 示
TRSpeciality_Course_Insert触发器的定义脚本 。
3,查看触发器的相关性通过系统存储过程 sp_depends查看触发器中数据的相关性 。 例如：执行
sp_depends 'TRSpeciality_Course_Insert',就可显示
TRSpeciality_Course_Insert触发器定义的脚本中所指定的对象引用 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.2 修改触发器
1,使用 T-SQL语句修改触发器使用 ALTER TRIGGER命令可以修改触发器 。 修改触发器的 ALTER TRIGGER命令的语法格式与创建触发器的 CREATE TRIGGER命令的语法相似,只是将命令的开始标志从 CREATE TRIGGER改为 ALTER TRIGGER即可,
命令中的参数与 CREATE TRIGGER命令完全相同,这里不再复述 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.2 修改触发器
2,使用企业管理器修改触发器
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改触发器的数据库 （ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要修改触发器的表 。
4） 右击要在其上修改触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,菜单项,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择需修改的触发器 。
6） 系统在,文本,文本框中显示出该触发器的定义脚本 。
7） 按需要在,文本,文本框中修改触发器的定义脚本 。
8） 单击,检查语法,按钮,检查触发器中语句的合法性,直到,语法检查成功,。
9） 单击,应用,→,确定,按钮,就可完成触发器的修改工作 。
第
9
章触发器
< >
9.5 删除触发器
9.5.1 使用 T-SQL语句删除触发器使用 DROP TRIGGER命令可以在当前数据库中删除一个或多个触发器 。 DROP TRIGGER命令的语法格式如下：
DROP TRIGGER { trigger_name } [,...n ]
例如,要删除 TRSpeciality_Course_Insert
触发器,可以使用以下命令实现：
DROP TRIGGER TRSpeciality_Course_Insert
第
9
章触发器
< >
9.5 删除触发器
9.5.2 使用企业管理器删除触发器
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中删除触发器的数据库 （ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要删除触发器的表 。
4） 右击要删除触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,菜单项,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择需删除的触发器名称 。
6） 单击,删除,按钮,出现,确实要删除触发器,确认对话框,单击,确定,,就可完成触发器的删除工作 。
第
9
章触发器
< >
9.1 触发器的概念
9.2 触发器的种类与工作原理
9.3 创建触发器
9.4 查看、修改触发器
9.5 删除触发器
9.1 触发器的概念
SQL Server中除可以使用约束和规则强制实施数据完整性外，还可以使用触发器实现数据的完整性。
触发器 （ TRIGGER） 是一种实施数据完整性的特殊类型存储过程,它不同于前面介绍过的存储过程,兼有强制参照完整性和存储过程的双重特性 。 在对表执行 UPDATE,INSERT,DELETE命令时,SQL Server就会自动触发执行触发器所定义的 SQL语句,从而确保对数据的处理必须符合由这些 SQL语句所定义的规则,
这相当于执行了一个存储过程 。 但触发器与一般的存储过程又有不同,存储过程是数据库对象,一般由用户直接调用执行 （ 除自动执行存储过程 ） ；而触发器依赖于数据表,只能通过操作 UPDATE,INSERT、
DELETE等命令触发,用户不能直接调用执行 。
第
9
章触发器
< >
9.1 触发器的概念触发器的主要功能是能够实现由主键与外键所不能保证的复杂的参照完整性和数据的一致性 。 此外,还有以下功能：
（ 1） 强化约束 。 触发器能够实现比 CHECK 语句更为复杂的约束 。
（ 2） 跟踪执行 。 触发器可以侦测数据库中内在的操作,从而不允许未经许可的插入,更新和删除操作影响数据库 。
（ 3） 级联运行 。 触发器可以侦测数据库内的操作,并自动地级联影响整个数据库中的各项内容 。
（ 4） 存储过程的调用 。 为了响应数据库更新,触发器可以调用一个或多个存储过程 。
因此,触发器可以解决高级形式的业务规则或复杂的行为限制以及实现定制记录等方面的问题 。 触发器能够根据某表数据修改前后状态所发生的差异,执行相应的处理 。 同时一个表的同一类型 （ INSERT,UPDATE,DELETE） 的多个触发器能够对同一种数据操作采取多种不同的处理 。
第
9
章触发器
< >
9.2 触发器的种类与工作原理
9.2.1 触发器的种类
SQL Server 2000支持两种类型的触发器,AFTER触发器和
INSTEAD OF触发器 。
l AFTER 触发器
AFTER 触发器即为 SQL Server 2000版本以前所介绍的触发器 。 该类型触发器要求只在执行了某一操作 （ INSERT,UPDATE、
DELETE） 之后,触发器才被触发,且只能在表上定义 。 可以为针对表的同一操作定义多个触发器 。 对于 AFTER触发器,可以定义哪一个触发器被最先触发,哪一个被最后触发,通常使用系统过程 sp_settriggerorder 来完成此任务 。
l INSTEAD OF触发器
INSTEAD OF触发器表示并不执行命令所定义的操作 （ INSERT、
UPDATE,DELETE）,而仅是执行触发器中定义的代码 。 它既可在表上定义,也可以在视图上定义,但对同一操作只能定义一个 INSTEAD OF触发器 。
第
9
章触发器
< >
9.2 触发器的种类与工作原理
9.2.2 触发器的工作原理当向表执行 INSERT,UPDATE或 DELETE语句时,若该表设置了触发器,则 SQL Server将根据不同的操作自动生成一个或二个临时表,Inserted表或 Deleted表,
同 时 将 操 作 数 据 送 入 触 发 器 表,Inserted表或
Deleted表中 。
Inserted表和 Deleted表是 SQL Server为触发器语句创建的逻辑表,存储在内存中,不是存储在数据库中,因此不允许用户直接对其修改 。 它们的结构与定义触发器相关表的结构相同,用于保存用户操作可能改变的行的新值或旧值,由系统自动维护 。 当触发器工作完成后,与之相关的逻辑表将自动删除 。
第
9
章触发器
< >
9.3 创建触发器触发器既可以使用 T-SQL语句创建,也可以在企业管理器中创建 。
创建触发器时,需要确定触发器的以下三个组成部分：
l 确定在其上定义触发器的表；
l 触发器将何时激活 。 指明激活触发器的数据修改语句,有效选项为 INSERT,UPDATE 或 DELETE。
多个数据修改语句可激活同一个触发器；
l 执行触发操作所执行的 T-SQL语句 。
第
9
章触发器
< >
9.3 创建触发器
9.3.1 使用 T-SQL语句创建触发器创建触发器的 CREATE TRIGGER语句的语法格式如下：
CREATETRIGGERtrigger_name
ONtable_name| view_name
[ WITHENCRYPTION]
{ { { FOR | AFTER | INSTEAD OF } { [INSERT][,]
[DELETE][,][UPDATE]}
[ NOTFORREPLICATION]
AS
[ { IFUPDATE( column)
[ { AND | OR} UPDATE( column) ] [,..n]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator }
updated_bitmask)
{ comparison_operator} column_bitmask[,..n] } ]
sql_statement[,..n]
}}
第
9
章触发器
< >
9.3 创建触发器
1,创建 INSERT触发器
【 例 9-1】 为成绩表 （ Grade） 创建一个 INSERT触发器,用于自动计算学生已修的学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Insert')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Insert
GO
CREATE TRIGGER TRGrade_Insert /* 触发器名 */
ON Grade /* 作用的表 */
FOR INSERT /* 创建 INSERT触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @CreditHour TINYINT /* 学分 */
DECLARE @Grade TINYINT /* 成绩 */
SELECT @Grade=G.Grade,@CreditHour=C.CreditHour
FROM Grade G INNER JOIN Inserted I
ON G.StudentID = I.StudentID and G.CourseID = I.CourseID
INNER JOIN Course C /* 按 CourseID联接 Course */
ON C.CourseID = I.CourseID
IF (@Grade>=60) /* 成绩大于等于 60 */
BEGIN
UPDATE Student/* 在已修学分中加上插入课程的学分 */
SET CreditHour=CreditHour + @CreditHour
FROM Student S,Inserted I
WHERE S.StudentID = I.StudentID
END
GO
第
9
章触发器
< >
9.3 创建触发器
2,创建 DELETE触发器
【 例 9-2】 为成绩表 （ Grade） 创建一个 DELETE触发器,用于自动计算学生已修的学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Delete')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Delete
GO
CREATE TRIGGER TRGrade_Delete /* 触发器名 */
ON Grade /* 作用的表 */
FOR DELETE /* 创建 DELETE触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @CreditHour TINYINT /* 学分 */
DECLARE @Grade TINYINT /* 成绩 */
DECLARE @StuID VARCHAR(10)
DECLARE @CourseID VARCHAR(8)
DECLARE @Gradeo TINYINT
SELECT * FROM Deleted /* 查看删除的数据,调试成功后该行需删除 */
SELECT @Grade=D.Grade,@CreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@Grade>=60) BEGIN /* 成绩大于等于 60 */
UPDATE Student/* 已修学分累计 */
SET CreditHour=CreditHour - @CreditHour
FROM Student S,Deleted D
WHERE S.StudentID = D.StudentID
END
GO
第
9
章触发器
< >
9.3 创建触发器
3,创建 UPDATE触发器
【 例 9-3】 为成绩表 （ Grade） 创建一个 UPDATE触发器,用于自动计算相关学生的已修学分 。
USE Educational
GO
-- 如果已存在同名触发器,则先进行删除操作
IF exists(SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'TRGrade_Update')
and OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER TRGrade_Update
GO
CREATE TRIGGER TRGrade_Update /* 触发器名 */
ON Grade /* 作用的表 */
FOR UPDATE /* 创建 UPDATE触发器 */
AS
第
9
章触发器
< >
9.3 创建触发器
DECLARE @OldStuID VARCHAR(10),@OldCourseID VARCHAR(8)
/* 修改前的旧数据 */
DECLARE @OldGrade TINYINT,@OldCreditHour TINYINT
DECLARE @NewStuID VARCHAR(10),@NewCourseID VARCHAR(8)
/* 修改后的新数据 */
DECLARE @NewGrade TINYINT,@NewCreditHour TINYINT
-- 取修改后的数据
SELECT @NewStuID=G.StudentID,@NewCourseID=G.CourseID,
@NewGrade=G.Grade,@NewCreditHour=C.CreditHour
FROM Grade G INNER JOIN Inserted I
ON G.StudentID = I.StudentID
and G.CourseID = I.CourseID INNER JOIN Course C /* 按
CourseID联接 Course */
ON I.CourseID = C.CourseID
第
9
章触发器
< >
9.3 创建触发器
-- 取修改前的数据
SELECT @OldStuID=D.StudentID,@OldCourseID=D.CourseID,
@OldGrade=D.Grade,@OldCreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@NewStuID=@OldStuID) /* 同一个学生 */
BEGIN
IF (@NewGrade>=60 AND @OldGrade>=60) /* 新成绩 >=60而旧成绩 <=60*/
UPDATE Student /* 从已修学分中上加上新课程的学分,
再减去旧课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour -
@OldCreditHour
WHERE StudentID = @NewStuID
ELSE
IF (@NewGrade>=60 AND @OldGrade<60) /* 新成绩 >=60而旧成绩 <60*/
UPDATE Student /* 从已修学分中加上新课程的学分 */
第
9
章触发器
< >
9.3 创建触发器
-- 取修改前的数据
SELECT @OldStuID=D.StudentID,@OldCourseID=D.CourseID,
@OldGrade=D.Grade,@OldCreditHour=C.CreditHour
FROM Deleted D INNER JOIN Course C /* 按 CourseID联接
Course */
ON D.CourseID = C.CourseID
IF (@NewStuID=@OldStuID) /* 同一个学生 */
BEGIN
IF (@NewGrade>=60 AND @OldGrade>=60) /* 新成绩 >=60而旧成绩 <=60*/
UPDATE Student /* 从已修学分中上加上新课程的学分,
再减去旧课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour -
@OldCreditHour
WHERE StudentID = @NewStuID
ELSE
第
9
章触发器
< >
9.3 创建触发器
IF (@NewGrade>=60 AND @OldGrade<60) /* 新成绩 >=60而旧成绩 <60*/
UPDATE Student /* 从已修学分中加上新课程的学分 */
SET CreditHour=CreditHour + @NewCreditHour
WHERE StudentID = @NewStuID
ELSE
IF (@NewGrade<60 AND @OldGrade>=60) /* 新成绩 <60而旧成绩 >=60*/
UPDATE Student /* 从已修学分中减去旧课程的学分 */
SET CreditHour=CreditHour - @OldCreditHour
WHERE StudentID = @NewStuID
END
ELSE /* 不同的学生 */
第
9
章触发器
< >
9.3 创建触发器
BEGIN
IF (@NewGrade>=60) /* 成绩大于等于 60 */
UPDATE Student /* 从已修学分中加上修改后课程的学分
*/
SET CreditHour=CreditHour + @NewCreditHour
FROM Student
WHERE StudentID = @NewStuID
IF (@OldGrade>=60) /* 成绩大于等于 60 */
UPDATE Student /* 从已修学分中减去修改前课程的学分
*/
SET CreditHour=CreditHour - @OldCreditHour
FROM Student
WHERE StudentID = @OldStuID
END
GO
第
9
章触发器
< >
9.3 创建触发器
9.3.2 使用企业管理器创建触发器通过企业管理器,可以方便地创建触发器,具体步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中创建触发器的数据库
（ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要创建触发器的表 。
4） 右击要在其上创建触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,命令,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择,新建,,系统 在,文本,文本框中显示了 CREATE TRIGGER语句的框架 。 在此修改触发器的名称,触发参数以及对应的 SQL语句,将例 9-4中的触发器代码输入 。
6） 单击,检查语法,按钮,检查触发器中语句的合法性,直到,语法检查成功,。
7） 单击,应用,→,确定,按钮,就可完成触发器的创建工作 。 此时,下位,名称,列 表 框,列 表 中 多 了 一 个 名 为
TRSpeciality_Course_Insert的 INSERT触发器,其前导图标为,”,说明创建 INSERT触发器成功 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.1 查看触发器
1,查看触发器的类型信息通过系统存储过程 sp_helptrigger查看指定表中定义的触发器类型信息 。
其语法格式为：
sp_helptrigger [ @tabname = ] 'table'
[,[ @triggertype = ] 'type' ]
2,查看触发器的定义文本通过系统存储过程 sp_helptext查看未加密触发器的定义文本 。 例如：
执行 sp_helptext 'TRSpeciality_Course_Insert',就 可 显 示
TRSpeciality_Course_Insert触发器的定义脚本 。
3,查看触发器的相关性通过系统存储过程 sp_depends查看触发器中数据的相关性 。 例如：执行
sp_depends 'TRSpeciality_Course_Insert',就可显示
TRSpeciality_Course_Insert触发器定义的脚本中所指定的对象引用 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.2 修改触发器
1,使用 T-SQL语句修改触发器使用 ALTER TRIGGER命令可以修改触发器 。 修改触发器的 ALTER TRIGGER命令的语法格式与创建触发器的 CREATE TRIGGER命令的语法相似,只是将命令的开始标志从 CREATE TRIGGER改为 ALTER TRIGGER即可,
命令中的参数与 CREATE TRIGGER命令完全相同,这里不再复述 。
第
9
章触发器
< >
9.4 查看、修改触发器
9.4.2 修改触发器
2,使用企业管理器修改触发器
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改触发器的数据库 （ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要修改触发器的表 。
4） 右击要在其上修改触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,菜单项,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择需修改的触发器 。
6） 系统在,文本,文本框中显示出该触发器的定义脚本 。
7） 按需要在,文本,文本框中修改触发器的定义脚本 。
8） 单击,检查语法,按钮,检查触发器中语句的合法性,直到,语法检查成功,。
9） 单击,应用,→,确定,按钮,就可完成触发器的修改工作 。
第
9
章触发器
< >
9.5 删除触发器
9.5.1 使用 T-SQL语句删除触发器使用 DROP TRIGGER命令可以在当前数据库中删除一个或多个触发器 。 DROP TRIGGER命令的语法格式如下：
DROP TRIGGER { trigger_name } [,...n ]
例如,要删除 TRSpeciality_Course_Insert
触发器,可以使用以下命令实现：
DROP TRIGGER TRSpeciality_Course_Insert
第
9
章触发器
< >
9.5 删除触发器
9.5.2 使用企业管理器删除触发器
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中删除触发器的数据库 （ 这里为 Educational） 节点 。
3） 单击,表,项,右边,项目,列表框中出现数据库中已存在的所有表名 。 在该列表框中找到要删除触发器的表 。
4） 右击要删除触发器的表,在弹出的快捷菜单中执行,所有任务,→,管理触发器,菜单项,出现如图 9-2所示的,触发器属性,对话框 。
5） 在,名称,列表框中选择需删除的触发器名称 。
6） 单击,删除,按钮,出现,确实要删除触发器,确认对话框,单击,确定,,就可完成触发器的删除工作 。
第
9
章触发器
< >
