SQL Server数据库技术及应用教程(SQL Server 2016)第13章 触发器

• UPDATE SelectCourse SET Score=120 WHERE Score=100.0
• GO • ⑥ 执行该语句，触发器的执行结果如图13-
19所示。
（3）创建DELETE触发器 【例13-6】 创建DELETE触发器，实现：如果某个学生退学，当删除Student表中 某个学生的记录时，SelectCourse表中相应学号的记录也被同时删除。操作步骤 如下。 ① 单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句： CREATE TRIGGER Tr_DeleteStudent
“消息”选项卡中显示，Student表中有1行受影响， SelectCourse表中有2行受影响。
（4）创建INSTEAD OF触发器 【例13-7】 在数据库StudentManagement中创建视图View_Score，包含学生的学号、姓名、课程 号和成绩。该视图依赖于Student表、Course表和SelectCourse表，是不可更新视图。可以在视图 上创建INSTEAD OF触发器，当向视图中插入数据时，分别向Student表和SelectCourse表插入数据， 从而实现向视图插入数据的功能。操作步骤如下。 ① 创建视图View_Score，单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句： USE StudentManagement GO CREATE VIEW View_Score AS SELECT StudentID,StudentName,CourseID,Score FROM Course INNER JOIN SelectCourse
• UPDATE Course SET Credits =6 WHERE CourseID='100300'
• GO • ④ 单击“执行”按钮，触发器的执行结果如
图13-9所示。
【例13-4】 创建触发器，当修改Student表中的学号时，同时也要将SelectCourse表中的学号修 改成相应的学号（假设两个表之间没有定义外键约束）。操作步骤如下。 ① 单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句： CREATE TRIGGER Tr_StudentID
• ON Course
• AFTER UPDATE
• AS
• IF UPDATE(Credits)
• BEGIN
•
PRINT '学分不能进行修改！'
•
ROLLBACK TRANSACTION
• END
• GO
• ② 单击“执行”按钮，完成触发器的创建，如图13-8所示。
• ③ 接着修改Course表中课程编号为100300的记 录的学分。单击“新建查询”按钮，在查询编 辑器中输入如下T-SQL语句：
• 显示“删除对象”对话框，如图13-13所示， 单击“确定”按钮。
• 删除SelectCourse表的两个外键后，执行下面TSQL语句：
• UPDATE Student SET StudentID='2019620288' WHERE StudentID='2019620208'
• GO • 单击“执行”按钮，执行结果如图13-14所示。
•
FROM inserted
• IF(@score<0 OR @score>100)
• BEGIN
•
PRINT '成绩的取值必须在0到100之间'
•
ROLLBACK TRANSACTION
• END
• ② 单击“执行”按钮，完成触发器的创建，如图13-16所示。
• ③ 接着修改SelectCourse表中的成绩，把75～ 79分（含）的成绩改为80。单击“新建查询” 按钮，在查询编辑器中输入如下T-SQL语句：
• IF(SELECT COUNT(*)
• FROM Student,inserted,Course
• WHERE Student.StudentID=inserted.SelectCourseStudentID
AND
•
Course.CourseID=inserted.SelectCourseID)=0
• INSERT SelectCourse • VALUES('2019310109','100200',100) • GO • ④ 单击“执行”按钮，触发器的执行结果如图13-6
所示。
• 假设该记录中的学号2019310149在Student表 中已经存在，代码如下：
• INSERT SelectCourse • VALUES('2019310149','100200',100) • GO • 由于外键约束冲突，因此触发器的执行结果如
• ③ 接着删除Student表中学号2019620288的学生记录。 单击“新建查询”按钮，在查询编辑器中输入如下 T-SQL语句：
• DELETE FROM Student WHERE StudentID='2019620288'
• GO • ④ 执行以上语句，执行结果如图13-21所示，在
• BEGIN
•
PRINT '输入的学号或课程编号错误'
•
ROLLBACK TRANSACTION
• END
• ② 单击“执行”按钮，完成触发器的创建，如图13-5所示。
• ③ 接着向SelectCourse表中插入一个记录，该记录中 的学号2019310109在Student表中不存在。单击“新 建查询”按钮，在查询编辑器中输入如下T-SQL语句：
• AS { sql_statement [ ; ] [,…n ] } • <dml_trigger_option> ::=[ ENCRYPTION ] [ EXECUTE AS Clause ]
（1）创建INSERT触发器 【例13-1】 建立插入数据触发器，实现当插入新学生的记录时，触发器将自动 显示“欢迎新同学！”的提示信息。操作步骤如下。 ① 单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句： CREATE TRIGGER Tr_Welcome
FROM inserted UPDATE SelectCourse
SET SelectCourseStudentID=@newStudentID WHERE SelectCourseStudentID=@oldStudentID END
• ③ 接着将Student表中学号为2019620208的学 生的学号修改为2019620288。单击“新建查询” 按钮，在查询编辑器中输入如下T-SQL语句：
• UPDATE Student SET StudentID='2019620288' WHERE StudentID='2019620208'
• GO • ④ 单击“执行”按钮，执行结果如图13-11所
示，提示错误是两个表之间有外键约束。
• 可以用下面的方法删除SelectCourse表的所 有外键：展开该表的“键”结点，右击要 删除的键名，从快捷菜单中选择“删除” 命令，如图13-12所示。
13.2 创建触发器
• 13.2.1 使用SSMS创建触发器 • 使用SSMS创建触发器的操作步骤如下。 • ① 启动SSMS，在对象资源管理器中展开数据
库StudentManagement下要创建触发器的表结 点（例如Student表）。 • ② 右击“触发器”结点，从快捷菜单中选择 “新建触发器”命令，如图13-1所示。
ON Student AFTER INSERT AS PRINT '欢迎新同学！' GO INSERT Student VALUES('2019310109','张芳','女','2001-0407','20193101','13701296525','zhangf@',' 辽宁',80) GO
ON Student AFTER UPDATE AS BEGIN DECLARE @oldStudentID CHAR(10), @newStudentID CHAR(10) SELECT @oldStudentID=StudentID
FROM deleted SELECT @newStudentID=StudentID
下：
• CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view }
• [WITH <dml_trigger_option> [,…n ]]{ FOR|AFTER|INSTEAD OF} {[ INSERT][,] [ UPDATE ] [,] [ DELETE ] }
ON Student FOR DELETE AS DECLARE @studentID CHAR(10) SELECT @studentID=StudentID
FROM deleted DELETE FROM SelectCourse
WHERE SelectCourse.SelectCourseStudentID=@studentID GO ② 单击“执行”按钮，完成触发器的创建，如图13-20所示。
图13-7所示。
• （2）创建UPDAe表中建立一个UPDATE触发器，当用户修改课程的学分 时，显示不允许修改学分的提示。操作步骤如下。
• ① 单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句：
• CREATE TRIGGER TR_Credits
第13章 触 发 器
13.1 触发器的基本概念
• 13.1.1 触发器的类型 • 1．DML触发器 • DML触发器又分为AFTER触发器和INSTEAD OF
触发器两种。 • （1）AFTER触发器 • （2）INSTEAD OF触发器 • 2．DDL触发器
• 13.1.2 触发器的优点 • 使用触发器主要优点如下： • ① 实现数据库中相关表的层叠更改。 • ② 强制使用更为复杂的约束。 • ③ 评估数据修改前后的表状态并采取对策。 • ④ 使用自定义的错误信息。 • ⑤ 维护非规范化数据。
• ③ 打开触发器脚本编辑窗格，显示新建触 发器的模板，如图13-2所示。在该窗格中输 入要创建的触发器的代码，输入完成后单
击“执行”按钮。若执行成功，则创建完 成。
• 13.2.2 使用T-SQL语句创建触发器 • • 1．创建DML触发器 • 使用CREATE TRIGGER语句可以创建DML触发器，其语法格式如
• UPDATE SelectCourse SET Score=80 WHERE Score>=75 AND Score<=79
• GO • ④ 单击“执行”按钮，触发器的执行结果如
图13-17所示。
• 修改后，SelectCourse表中的记录如图13-18所示。
• ⑤ 在查询编辑器中输入如下T-SQL语句：
• 【例13-2】 建立INSERT触发器，确保SelectCourse表的 参照完整性，以维护其外键与参照表中的主键一致。操 作步骤如下。
• ① 单击“新建查询”按钮，在查询编辑器中输入如下TSQL语句：
• CREATE TRIGGER Tr_SelectCourse • ON SelectCourse • FOR INSERT • AS
在“消息”选项卡中显示SelectCourse表中有2 行被修改，Student表中有1行被修改。
• 在修改Student表中的学号的同时， SelectCourse表中的学号也被修改成相应的 学号。修改后，Student表和SelectCourse表 中的记录如图13-15所示。
• 【例13-5】 创建一个触发器，当插入或更新SelectCourse表中的成绩时，该触发器检查插入或更新的数据 是否处于设定的数值范围0～100内。操作步骤如下。
• ① 单击“新建查询”按钮，在查询编辑器中输入如下T-SQL语句：
• CREATE TRIGGER Tr_Score
•
ON SelectCourse
•
FOR INSERT,UPDATE
• AS
• DECLARE @score NUMERIC(4,1)
•
SELECT @score=inserted.Score