【SQL】实验十一 触发器的使用

一、实验目的

1.了解触发器的概念、特点和作用。

2.掌握SQL SERVER 创建和管理触发器的方法。

二、实验内容及要求

本实验使用数据库为SCHOOL数据库。

l．为deparment表增加一列teac_num用于统计各个系教师的人数。

alter table Deparment
add teac_num int

2．更新teac_num列，让其正确统计当前数据库中各个系教师的人数。

update Deparment
set teac_num=
(select count(*)
 from teacher
 where Deparment.Depar_id=Teacher.Depar_id
 group by Depar_id)

3．在Teacher表中创建一个insert触发器trigger_in_teacher，使得某系增加新教师时，该系对应的teac_num列自动变化。

create trigger trigger_in_teacher
on Deparment 
for insert
as
begin
  update Deparment set teac_num=teac_num+
  (select count(*) from inserted
   where Deparment.Depar_id=inserted.Depar_id)
end

4．创建一个update触发器trigger_update_course，修改课程表中Course中的某门课的课程号时，对StudentGrade，CourseTeacher中课程号也做相应修改。

create trigger tigger_update_course
on Course
for update
as
begin
   update StudentGrade  set StudentGrade.Course_id=Course.Course_id 
   from Course,StudentGrade

   update CourseTeacher  set CourseTeacher.Course_id=Course.Course_id 
   from Course,CourseTeacher
end

测试该触发器：将“数据库”的课程号改为“1110”，修改能否顺利执行？如不能，原因是什么？

update Course
set Course_id='1110' 
where Course_name='数据库'

消息 547，级别 16，状态 0，第 1 行
UPDATE 语句与 REFERENCE 约束"FK_StudentGrade_Course"冲突。该冲突发生于数据库"school"，表"dbo.StudentGrade", column 'Course_id'。
语句已终止。

不能顺利执行，因为Course表的外键约束冲突，删除外键约束即可解决

 

5．创建一个触发器trigger_course，当修改课程数据表course中的数据时（包括插入、更新和删除操作），显示提示信息“课程表被修改了”。

create trigger tigger_course
on Course
for update,delete,insert
as
begin
  print'课程表被修改了'
end

测试该触发器：将“数据库”的学时改为80，将“数据结构”的课程号改为“5010”，修改能否顺利执行？系统有何反应？如不能，原因是什么？

update Course
set Course_hour=80 
where Course_name='数据库'

update Course
set Course_id=5010
where Course_name='数据结构'

 第一个可执行，第二个报错如下

 消息 2627，级别 14，状态 1，过程 tigger_update_course，行 6 [批起始行 0]
违反了 PRIMARY KEY 约束“PK_StudentGrade”。不能在对象“dbo.StudentGrade”中插入重复键。重复键值为 (000503001, 0101)。
语句已终止。

不能顺利执行，违反了StudentGrade的主键约束 

测试该触发器：将“古代汉语”删除，删除能否顺利执行？系统有何反应？如不能，原因是什么？

delete
from Course
where Course_name='古代汉语'

消息 547，级别 16，状态 0，第 2 行
DELETE 语句与 REFERENCE 约束"FK_StudentGrade_Course"冲突。该冲突发生于数据库"school"，表"dbo.StudentGrade", column 'Course_id'。
语句已终止。

不能顺利执行，与StudentGrade表的外键约束冲突（通过外键把Course表和StudentGrade连接起来了）

 

6．删除trigger_ course触发器。

drop trigger tigger_course

 

 

三、实验小结

1．触发器的主要作用是实现数据的完整性和一致性。如何正确使用触发器？

创建触发器时需指定： 名称、在其上定义触发器的表、触发器将何时激发、激活触发器的数据修改语句。

主要用于 强制数据完整性，保证数据一致性

2.触发器可以划分为几种类别？分别是什么？

分为两类：alter触发器和instead of触发器

AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都成功执行后才激发。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。只指定FOR时，默认为AFTER。 INSTEAD OF：指定执行触发器而不是执行“触发 SQL 语句”，从而替代“触发语句”的操作。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。

3. 此次实验中得到的哪些经验教训、疑难问题？有什么心得或总结？

细心，认真，总结