事务

---事务:保障整个流程的完整执行，全部没有问题，统一提交，一旦有问题，回到原点。

begin tran --事务的开始
--开始写流程语句
--语句写完之后
if @@ERROR>0
begin
rollback tran--回滚事务
end
else
commit tran --提交事务




--级联删除套入事务中
alter trigger score_delete
on score
instead of delete
as
    declare @count int
    select @count=count(*)from deleted
    
    declare @i int
    set @i=0
    while @i<@count --循环遍历delete临时表的数据，然后转移到备份表中
    begin
    declare @sno varchar(20)
    declare @cno varchar(20)
    declare @degree decimal(4,1)
    select top 1 @sno=sno,@cno=cno,@degree=degree from deleted where 
    sno not in (select top (@i) sno from deleted)
    or cno not in(select top (@i) cno from deleted)
    
    --下面开始事务
    begin tran
    insert into scorebak values(@sno,@cno,@degree,getdate())
    delete from score where sno =@sno and @cno=@cno
    if @@error>0
    begin
    rollback tran
    end
    else
    commit tran
    
    
    set @i=@i+1
    end
go

delete from score where Sno=109
select*from scorebak
drop table scorebak