实验六 触发器(2012new)

实验六触发器
学号：姓名：专业：班级：一、实验目的
（1）理解触发器的用途、类型和工作原理。

（2）掌握利用T-SQL语句创建和维护触发器的方法。

（3）掌握利用SQL Server Management Studio创建、维护触发器的方法。

二、实验内容
1.创建AFTER触发器
(1）创建一个在插入时触发的触发器sc_insert：当向sc表插入数据时，须确保插入的学号已在student表中存在，并且还须确保插入的课程号在course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件。

（注：student表与sc表的外键约束，course表与sc表的外键约束要先取消）。

触发器的创建语句：if exists(select name from sysobjects
where name='sc_insert'and type='tr')
drop trigger sc_insert
go
create trigger sc_insert on sc for insert
As
declare @sno char(8),@cno char(3)
Select @sno=sno,@cno=cno from inserted
If not exists(select*from student where sno=@sno)
Begin
print'not exist the student'
If not exists(select*from course where cno=@cno)
print'not exist the course'
Rollback tran
End
else if not exists(select*from course where cno=@cno)
begin
Rollback tran
print'not exist the course'
end
go
触发器的触发执行测试结果：
(2）为course表创建一个触发器course_del，当删除了course表中的一条课程信息时，同时将表sc表中相应的学生选课记录也一并删除。

触发器的创建语句:create trigger course_del on course for delete
As declare @cno char(3)
Select @cno=cno from deleted
Delete from sc where cno=@cno
触发器的触发执行测试结果：
(3）在course表中添加一个平均成绩avg_grade字段（记录每门课程的平均成绩），创建一个触发器grade_modify，当SC表中的某学生的成绩发生变化时，则course表中的平均成绩也能及时相应发生改变。

触发器的创建语句：create trigger grade_modify
on sc for update
as if update(grade)
begin
update course
set avg_grade=(select avg(grade)from sc
where o=o
group by cno)
end
触发器的触发执行测试结果：
2.创建INSTEAD OF触发器
(1）创建一视图student_view,包含学号、姓名、课程号、课程名、成绩等属性，在student_view上创建一个触发器grade_moidfy，当对student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。

触发器的创建语句：create trigger grade_moidfy
on student_view
instead of insert
as declare @grade tinyint,@sno char(8),@cno char(3)
if update(grade)
begin
select @grade=grade,@sno=sno,@cno=cno from inserted
update sc
set grade=@grade
where sno=@sno and cno=@cno
end
触发器的触发执行测试结果：
(2）在Student表中插入一个getcredit字段（记录学生所获学分的情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与course表中的值一致；如果新成绩小于60分，则该生未能获得学分，修改值为0。

触发器的创建语句：create trigger ins_credit
on sc for update,insert
as declare @sno char(8),@cno char(3),@grade tinyint,
@credit tinyint
if update(grade)
begin
select @grade=grade,@cno=cno,@sno=sno
from inserted
select
if @grade>=60
begin
select @credit=credit from course where cno=@cno
update student
set getcredit=@credit where sno=@sno
end
else
update student
set getcredit=@credit where sno=@sno
end
触发器的触发执行测试结果：
3. 使用T-SQL语句管理和维护
(1）用系统存储过程sp_helptrigger查看触发器grade_modify的相关信息。

结果：EXECUTE sp_helptrigger grade_modify
结果：EXECUTE sp_helptext grade_modify
(3) 使用select语句查看触发器grade_modify的定义内容。

（提示：通过查询表sysobjects和表syscomments，触发器的名称保存在表sysobjects 的name列，定义脚本保存在表syscomments的text列）
(select text from sysobjects, syscomments where name=’grade_modify’and sysobjects.id=syscomments.id)
结果：select text from sysobjects,syscomments
where name='grade_modify'and sysobjects.id=syscomments.id
（4）用系统存储过程sp_depends查看触发器grade_modify的相关性（即该触发器涉及到哪些基本表）。

（提示：sp_depends ‘grade_modify’查看某个对象被其他调用（或引用）的关系）结果：EXECUTE sp_depends'grade_modify'
（5）将sc_insert触发器改为instead of触发器，实现的功能不变。

（提示：使用alter trigger 语句修改）
结果：alter trigger sc_insert
ON SC
INSTEAD OF INSERT
AS
declare @Sno char(8),@Cno char(3)
SELECT @Sno=Sno,@Cno=Cno
FROM INSERTED
IF @Sno NOT IN
(SELECT Sno
FROM student
WHERE @Sno=Sno)
BEGIN
ROLLBACK TRAN
RAISERROR('Sno is not in student',16,10)
END
IF @Cno NOT IN
(SELECT Cno
FROM Course
WHERE @Cno=Cno)
BEGIN
ROLLBACK TRAN
RAISERROR('Cno is not in Course',16,10) END
GO
（6）将触发器sc_insert删除。

（提示：使用drop trigger 语句删除）
结果：drop trigger sc_insert
4.使用SQL Server Management Studio管理存储过程
（1）在SQL Server Management Studio中重新创建先前删除的触发器sc_insert。

结果：
（2）查看触发器sc_insert的内容。

结果：select text from sysobjects,syscomments
where name='sc_insert'and sysobjects.id=syscomments.id
（3）删除触发器sc_insert。

结果：drop trigger sc_insert。