触发器基础实验
将实验四的数据库按书中79页中的表对其填充数据,然后在数据库中做如下操作:
- 查询学号为201215121的姓名和所选修的课程门数,当门数为2门及以上时,则输出“XX,已经完成了选课”,否则输出“XX,还需选课”。(提示:需定义变量存储选修的课程门数和学生姓名)
解:查询代码如下:
declare @Sname varchar(10),@num int
select @Sname=Sname,@num=COUNT(*)
from Student,SC
where Student.Sno=SC.Sno and SC.Sno='201215121'
Group by Sname
if @num>=3
print @Sname+',已经完成了选课'
else
print @Sname+',还需选课'
运行结果如下图:
- 在数据库中查找姓名为“张立”的学生,若找到,输出该学生的学号,姓名,年龄,院系,否则,输出“查无此人”的信息。(提示:用if exists)
解:代码如下:
declare @tname varchar(50)='张立'
if exists(select* from Student where Sname='张立')
select Sno as 学号,Sname as 姓名,Sage 年龄,Sdept as 院系 from Student
where Sname=@tname
else print'查无此人'
运行结果如下图:
- 在Student表上创建一个触发器,每次有插入操作时,都设置变量@str的值为"Trigger is working",并输出显示出来。
解:代码如下:
create trigger Lan on Student
after insert as begin
declare @str char(20)
set @str = 'Trigger is working'
print @str
end
运行结果如下图:
测试如下:
insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201512171','小明','男','20','IS')
- 按课本P166页的方式创建TEACHER表(在此之前需创建DEPT表)。并在此教师表中定义一个AFTER触发器,为教师表Teacher定义完整性规则“教授的工资不得低于4000元,如果低于4000元,自动改为4000元”。并进行触发器的验证。
解:代码如下图:
create table DEPT(Deptno numeric(2),
Dname char(9) unique not null,
Location char(10),
primary key(Deptno));
create table TEACHER(
Eno numeric(4) primary key,
Ename char(10),
Job char(8),
sal numeric(7,2),
Deduct numeric(7,2),
Deptno numeric(2),
constraint teacherkey foreign key(Deptno) references dept(deptno),
constraint c1 check(Sal +Deduct >=3000))
运行结果:
create trigger CN on teacher after insert,update as begin
declare @job char(8),@sal numeric(7,2),@eno numeric(4)
select @job=job,@sal=sal,@eno=eno from inserted
if (@job='教授'and @sal<4000)
update TEACHER set sal='4000'
where Eno=@eno
end
触发器验证:
insert into teacher(Eno,sal,job) values('100','2000','教授')
select * from teacher where Eno='100'
- 首先解除Student表和SC表的参照关系。然后在Student表上创建一个触发器,使更新一个学生的学号信息时能够级联的更新此学生在SC表中的选课记录的学号信息,并进行验证。
解:代码如下:
create trigger st_SC on Student after update,insert as begin
declare @new char(9),@old char(9)
select @new=sno from inserted
select @old=sno from deleted
update SC set Sno=@new where Sno=@old
end
运行结果如下:
update student set sno='201215191' where sno='201215121'
select * from student where Sno='201215191'
select * from sc where sno='201215191'
(6)首先解除Student表和SC表的参照关系。然后在Student表上创建一个触发器,使删除一个学生时能够级联的删除此学生在SC表中的选课记录,并进行验证。
解:代码如下:
create trigger del on Student after delete as begin declare @Sno char(9)
select @Sno = sno from deleted
delete from SC
where Sno=@Sno
end
delete from student where Sno='201215191'
slect * from student
select * from SC