数据库--事务:级联删除(学生教师信息表)为例

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

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

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。