数据库--事务：级联删除（学生教师信息表）为例

--事物：保障整个流程的完整执行，全部没有问题统一提交，一旦有问题，回到原点
--存储过程或者触发器里面用

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



---------------------------------级联删除为例

drop table Scorebak
drop trigger Score_Delete
--删除前先备份数据到备份表之后再删除
create table Scorebak
(
    ids int identity(1,1) primary key,
    sno varchar(50),
    cno varchar(50),
    degree decimal(4,1),
    dayetime datetime
)
create 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 
    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---------------------------------------------------------
        begin
            commit tran----------------------------------------------
        end        
        
        set @i=@i+1        
    end
go
select *from Scorebak
select *from Score
delete from Score where Cno=‘3-105‘

--事物：保障整个流程的完整执行，全部没有问题统一提交，一旦有问题，回到原点
--存储过程或者触发器里面用

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



---------------------------------级联删除为例

drop table Scorebak
drop trigger Score_Delete
--删除前先备份数据到备份表之后再删除
create table Scorebak
(
    ids int identity(1,1) primary key,
    sno varchar(50),
    cno varchar(50),
    degree decimal(4,1),
    dayetime datetime
)
create 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 
    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---------------------------------------------------------
        begin
            commit tran----------------------------------------------
        end        
        
        set @i=@i+1        
    end
go
select *from Scorebak
select *from Score
delete from Score where Cno=‘3-245‘