触发器:
–1当学生入学成绩成绩小于460时候,学生的录取类型为借读生。
CREATE TRIGGER Student_fenshu ON StudentInfor --该触发器建在学生入学表生
FOR INSERT --触发器类型 插入记录时候
as
declare e1 cursor for
select Sno,enrollmentScore,muqianZhuangtai from inserted --触发器名称 e1
declare @Sno char(12)
declare @enrollmentScore smallint
declare @sourceUpdate char(10)
OPEN e1
fetch NEXT FROM e1 into @Sno,@enrollmentScore,@sourceUpdate
WHILE @@FETCH_STATUS=0 – 当返回值为0时候继续执行Begin下语句,知道返回值为-1或者非0
BEGIN
IF @enrollmentScore <=460 --入过入学分数小于或者等于460
update StudentInfor set sourceUpdate=‘借读’ --把当前已经输入的入读类型为借读,即使之前是其他也会更改为借读
where @Sno=Sno
fetch next from e1 into @Sno,@enrollmentScore,@sourceUpdate --条件为0时候继续遍历临时表中的记录,知道条件为非0
end
close e1 --遍历游标 e1
DEALLOCATE e1 --释放游标
–2学籍管理,当学生毕业时候,在学籍变动表中,为学生发放毕业证
Create TRIGGER Student_biye ON xuejiUpdate
FOR insert --触发器类型 插入记录时候
as
declare e_biye cursor for
select Sno,updateType,SendOrYanZheng from inserted
declare @Sno varchar(36)
declare @updateType char(4)
declare @SendOrYanZheng VARCHAR(50)
OPEN e_biye
fetch NEXT FROM e_biye into @Sno,@updateType,@SendOrYanZheng
WHILE @@FETCH_STATUS=0
BEGIN
if @updateType=‘毕业’ --如果学籍异动表中出现一个新的异动类型为毕业
BEGIN
update xuejiUpdate set SendOrYanZheng=‘发给毕业证’ where @Sno=Sno --那么发给毕业证
END
fetch next from e_biye into @Sno,@updateType,@SendOrYanZheng
end
close e_biye
DEALLOCATE e_biye
–3当学生状态为毕业状态时候,自动把所有毕业生的信息导入到一张毕业生表中去。
CREATE TRIGGER biye_tig ON StudentInfor
FOR update
as
declare e_insertBiye cursor for select Sno,Sname,buBie,sourceUpdate from inserted
declare @Sno char(12)
declare @Sname varchar(36)
declare @buBie char(4)
declare @sourceUpdate char(10)
OPEN e_insertBiye
fetch NEXT FROM e_insertBiye into @Sno,@Sname,@buBie,@sourceUpdate
WHILE @@FETCH_STATUS=0
BEGIN
–当我更改变动类型时候,而且满足变动类型为毕业,那么毕业生信息导入到毕业生到BiyeSheng表中
if update(sourceUpdate) and @sourceUpdate=‘毕业’
insert into BiyeSheng values(@Sno,@Sname,@buBie)
fetch next from e_insertBiye into @Sno,@Sname,@buBie,@sourceUpdate
end
close e_insertBiye
DEALLOCATE e_insertBiye
–4当学生学籍状态发生改变时候例如请假转班或者进行毕业,
–把学籍异动信息进行存储到学籍异动表中,每次变动都会添加一条记录,主键是学号+日期。
Create TRIGGER Student_baingeng ON StudentInfor
FOR update
as
declare e cursor for select Sno,muqianZhuangtai,sourceUpdate from inserted
declare @Sno varchar(36)
declare @muqianZhuangtai char(10)
declare @sourceUpdate char(10)
OPEN e
fetch NEXT FROM e into @Sno,@muqianZhuangtai,@sourceUpdate
while @@FETCH_STATUS=