-
设计一个触发器，实现如下功能：在Student中, 当删除某一同学S#时，该同学的所有选课也都要删除；
完成触发器的创建
-
假设Student表中某一学生要变更其主码S#的值，如使其原来的98030101变更为99030131, 此时sc表中该同学已选课记录的S#也需自动随其改变。设计一个触发器完成上述功能；
完成触发器的创建并执行更新操作
-
设计一个触发器：当进行Teacher表更新元组时, 使其工资只能升不能降；
完成触发器创建及检验 工资低于原工资时无法更新
-
建立CourSum(S#, Sname, SumCourse)表,其中属性SumCourse统计该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1。设计一个触发器自动完成上述功能；
完成触发器的创建以及插入操作，看后续是否能计数
成功完成对每位同学的选课计数，再次插入单条选课记录，看是否能完成计数
- 设计一个触发器，实现以下功能：Dept(D#, Dname, Dean)表中Dean对应的教师，一定是该系教师Teacher(T#, Tname, D#, Salary)中工资最高的教师。
附：实验源码
1.create trigger shanchu on student
for delete
as
delete SC
from student,deleted
where SC.S#=deleted.sno
delete from student
where student.sno=98030101
2.create trigger gengxin on student
for update
as
if update(sno)
update SC set SC.S#=inserted.sno
from SC,deleted,inserted
where SC.S#=deleted.sno
update student
set sno=12345678
where sno=98030101
3.create trigger jiaxin on teacher
for update
as
begin
declare @x int
declare @y int
select @x=Salary from inserted
select @y=Salary from deleted
if(@x<@y)
begin
print ‘工资小于原工资，不行’
rollback transaction
end
else
print ‘nice!’
End
4.create trigger jilu on SC
for insert
as
declare @x char(10)
declare @y char(10)
select @x=S# from inserted
select @y=C# from inserted
declare @Sumcourse int
select @Sumcourse=count(*)
from SC
where S#=@x
insert coursum
select S#=@x,C#=@y,Sumcourse=@Sumcourse
5.create trigger xizhurenNB on teacher
for insert
as
begin
declare @z char(10)
select @z=D#
from inserted
declare @x int
select @x=Salary
from inserted
declare @y int
select @y=Salary
from teacher,Dept
where Dept.Dean=teacher.Tname and @z=teacher.D#
if(@x>@y)
begin
print’***觉得工资不能大于系主任’
rollback transaction
end
end
