MSSM触发器和游标

题目：
1.使用游标+触发器使其操纵数据时保证成绩in (0,100)
2.使用游标实现级联删除

创建触发器＆声明游标

create trigger <触发器名称> on < table> for < operation name>
declare <游标名> for < select子句>

步骤

1. 创建触发器+as
2. 声明主变量，声明并打开游标
3. fetch -> while 循环 -> 判断(打印+fetch+continue) -> (打印+逆操作+fetch)while结束 -> 关闭游标

由于更新和插入逆操作不同（一个是旧值代替新值，一个是删除），需要分别写两个触发器

create trigger score_sc_tri on sc for insert
as 
declare @score_read tinyint, @sno char(10), @cno char(10)
declare c cursor for select score,sno,cno from inserted
open c
fetch next from c into  @score_read,@sno,@cno
while @@FETCH_STATUS=0
begin
	if @score_read>=0 and @score_read<=100
	begin 
		print 'ok'
		fetch next from c into @score_read,@sno,@cno
		continue
	end
		print 'error'
		delete from sc where @sno=sno and @cno=cno
		fetch next from c into @score_read,@sno,@cno
	
end
close c

create trigger score_sc_tri2 on sc for update
as 
declare @score_read tinyint, @sno char(10), @cno char(10),@score_old tinyint
declare c2 cursor for select score,sno,cno from inserted
select @score_old=score from inserted
open c2
fetch next from c2 into  @score_read,@sno,@cno
while @@FETCH_STATUS=0
begin
	if @score_read>=0 and @score_read<=100
	begin 
		print 'ok'
		fetch next from c2 into @score_read,@sno,@cno
		continue
	end
		print 'error'
		update sc set score=@score_old where @sno=sno and @cno=cno
		fetch next from c2 into @score_read,@sno,@cno
	
end
close c

效果如图

第二题

create trigger tri_c on c for delete
as
 declare @cno_del char(10) 
declare cur_c cursor for select cno from deleted
open cur_c
fetch next from cur_c into @cno_del
while @@FETCH_STATUS=0
begin
	delete from sc where cno=@cno_del
	print 'delete'
	fetch next from cur_c into @cno_del
	continue
end
 close cur_c