触发器的操作

触发器的操作

一、实验目的

掌握交互式和T-SQL创建、修改、删除及使用触发器的方法；
掌握触发器的功能。

二、实验知识要点

了解和掌握实验相关知识点：触发器是在用户要对某一表内的数据做插入INSERT、更新UPDATE、删除DELETE时被触发执行。通常我们使用触发器来检查用户对数据库表的更新是否合乎整个应用系统的需求和合乎商业规则以维持表内数据的完整性和正确性。
触发器的作用
（1）触发器可通过数据库中的相关表实现级联更改。
通过级联引用完整性约束可以更有效地执行这些更改。
（2）触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与CHECK 约束不同，触发器可以引用其它表中的列。触发器的主要好处在于它们可以包含使用 Transact-SQL 代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能
（3）触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。
（4）一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。
创建触发器的SQL语句
CREATE TRIGGER 触发器名 ON { 表名 | 视图名 }
{
{{ FOR |AFTER|INSTEAD OF }{[INSERT] [,UPDATE ] [,DELETE ]}
AS
[{ IF UPDATE( 列名 )[{ AND | OR }
UPDATE( 列名 ) ] [ …n ] }]
SQL语句 [ …n ]
}
}

三、实验内容

创建一个名为：jiaoxue的数据库，在该库下创建如下的各表以及视图等数据库对象。
STUDENT表

学 号	姓 名	性 别	年 龄	所 在 系
Sno	Sname	Ssex	Sage	Sdept
200215121	李勇	男	20	CS
200215122	刘晨	女	19	CS
200215123	王敏	女	18	MA
200515125	张立	男	19	IS
COURSE表
课程号	课程名	先行课	学分
---------------------------	---------------	--------------	------------
Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学	1	2
3	信息系统	6	4
4	操作系统	7	3
5	数据结构	6	4
6	数据处理		2
7	PASCAL语言		4
SC表
学号	课程号	成绩
---------------------------	---------------	--------------
Sno	Cno	Grade
200215121	1	92
200215121	2	85
200215121	3	88
200215122	2	90
200215122	3	80

四、实验操作及过程

--1、为表SC创建一触发器：当插入或修改一个记录时，确保此记录的成绩在 0—100之间。
use jiaoxue1
if object_id('tri_1','tr') is not null
drop trigger tri_1
go
CREATE TRIGGER tri_1
   ON sc
   for insert,update
AS 
BEGIN
	declare @grade int
	select @grade=grade from inserted
	if @grade>=0 and @grade<=100
	begin
		print 'ok'
		return 
	end
	else
	begin
		rollback transaction
		print 'no'
	end
END
GO
insert into sc values('1','0',11)
go
insert into sc values('1','3',110)
go
--2、为学生表STUDENT创建一触发器：男同学年龄不能超过22周岁，女同学CS系年龄不超过20岁，其他女同学年龄不能超过21岁。
use jiaoxue1
if object_id('tri_3','tr') is not null
drop trigger tri_3
go
CREATE TRIGGER tri_3
   ON student
   for insert,update
AS 
BEGIN
	declare @sage int,@ssex char(2),@sdept char(10)
	select @sage=sage from inserted
	select @ssex=ssex,@sdept=sdept from student
	if @ssex='男' and @sage<=22 and @sage>0 
		begin
		print '操作完成'
		end
	else if @ssex='女' and @sdept='CS' and @sage<=20 and @sage>0
		begin 
		print '操作完成'
		end
	else if @ssex='女' and @sage<=21 and @sage>0
		begin 
		print '操作完成'
		end
	else
		print '记录数据错误！请重新输入'
		rollback Transaction
END
GO
insert into student values('1','齐','男',20,'CS')
--3、为表COURSE、表SC和表STUDENT创建参照完整性：级联删除和级联修改触发器。
use jiaoxue1
if object_id('student_sc','tr') is not null
drop trigger student_sc
go
CREATE TRIGGER student_sc
   ON student
   for update,delete
AS 
BEGIN
	if exists(select 1 from inserted)
		update sc set sc.sno = (select sno from inserted) where sc.sno in (select sno from deleted)
	else
		delete sc where sc.sno in (select sno from deleted)
END
GO
--删除操作
delete from student where sno='201215122'
--更新操作
update student set sno='201215125' where sno='201215121'
--4、为新建表TEACHER(TNO,TNAME,PROF,SAL,COMM)含义为：教师编号、姓名、职称、基本工资和岗位津贴，创建一触发器：当职称从“助教”晋升为“讲师”时，岗位津贴自动增加200元；当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加500元；从“副教授”晋升为“教授”时，岗位津贴自动增加900元。
use jiaoxuedb
if object_id('teacher_1','tr') is not null
drop trigger teacher_1
go
CREATE TRIGGER teacher_1
   ON  teacher
   for update as if update(prof)
BEGIN
declare @prof1 char(10),@prof2 char(10),@tno char(10)
select @prof1=prof,@tno=tno from deleted
select @prof2=prof from inserted
	if @prof1='助教' and @prof2='讲师'
	update teacher set comm = comm + 200 where tno=@tno
	if @prof1='讲师' and @prof2='副教授'
	update teacher set comm = comm + 500 where tno=@tno
	if @prof1='副教授' and @prof2='教授'
	update teacher set comm = comm + 900 where tno=@tno
END
GO
update  teacher set prof='讲师' where tn='田峰'
--补充
--若修改SC表中一记录的课号，则要检查表course中是否存在与该课号相同的记录，若有则不许修改，若无则可修改
CREATE TRIGGER TRIGGER_sc
   ON  sc
   for update
AS 
if update(cno)
BEGIN
	declare @cno_N char(2),@cno_O char(2),@cno_1 int
	select @cno_N=cno from deleted
	select @cno_1=count(*) from course where cno=@cno_O
	if @cno_1<>0
		rollback transaction
END
GO
--course触发器
use jiaoxue1
if object_id('tri_2','tr') is not null
drop trigger tri_2
go
CREATE TRIGGER tri_2
   ON course
   for delete,update
AS 
BEGIN
	declare @cno1 char(10),@cno2 char(10)
	select @cno1=cno from deleted
	select @cno2=cno from inserted
	if (select count(*) from inserted)=0
		begin
		delete from sc where cno=@cno1
		--delete from tc where cno=@cno1
		end
	else
		begin 
		update sc set cno=@cno2 where cno=@cno1
		--update tc set cno=@cno2 where cno=@cno1
		end
END
GO
delete from course where cno='1'
update course set cno='0' where cno='1'

五、实验小结

本次实验中，首先通过看视频，看书掌握触发器的创建，修改，删除以及其它的一些最基本的操作。
遇到的问题是 第三题中的为三个表创建级联删除和级联修改的触发器，建立时应该建立在主键所在的表上，通过删除或修改SC表中的信息，相关STUDENT和COURSE表中的相关信息也进行修改。最后一题中应注意通过添加教师编号来确定相应老师的职称改变其相关信息进行修改。