sql server 删除表中记录触发器判断 sql触发器删除数据

转载

AI大梦想家 2024-06-15 21:25:56

create trigger Course_Delete
on course
instead of delete
as
    
    declare @cno varchar(20)  --定义变量
    select @cno = cno from deleted        --临时表里的信息是instead of 替换 delete要删除的信息
    delete from score where cno = @cno    --instead of 替换操作后执行的代码命令
    delete from course where cno=@cno
go
delete from course where cno='3-245'---'3-245'为输入的参数
select *from score
select *from course
--上面的这个触发器存储过程只能删除一条一条的信息
  
  
  
  
drop trigger course_delete
create trigger Course_Delete
on course
instead of delete
as
    delete from score where cno in (select cno from deleted)
    delete from course where cno in (select cno from deleted)
go
delete from score where cno='3-105'
select*from course
select*from score
--这个触发器可以删除多条信息



--次性删除course表数据，使用触发器替换删除操作，先删除外键表相关数据，再删除course
--删除前先备份数据到备份表之后再删除
drop trigger score_delete
drop table scorebak
create table scorebak  --建一个备份表
(
    ids int identity(1,1) primary key ,
    sno char(3),
    cno char(5),
    degree decimal(4,1),
    datetimes datetime
)
create trigger Score_delete  --建一个触发器的存储过程
on score                      --在成绩表里
instead of delete               --把declete *from  course 命令替换掉然后执行触发器存储过程
as
    declare @count int   --定义一个变量
    select @count=count(*) from deleted  --把临时表里的行数赋给@count 这个变量。被删除的这个表里的信息都在deleted这个临时表里。
                                          --把表里的行数付给变量
    declare @i int   --再定义一个变量
    set @i=0         --for(i=0;i<count;i++)
    while @i<@count --循环遍历deleted临时表的数据，然后转移
    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)        -- 如果@i是2的时候，去除前两页，显示第一页
        or cno not in (select top (@i) cno from deleted )
       -- or degree not in(select top(@i) degree from deleted)
       
        insert into scorebak values(@sno,@cno,@degree,getdate())  --向临时表中插入数据
        
        delete from score where sno=@sno and cno=@cno --每向临时表中插入一条数据，就把score表里的数据删除一条
                   --因为是要删除course表，而course表是主键，所以要删除外键score表的信息，而score表的信息并没有被要求删除，
                   -- 所要要建一个临时表，把score表里信息添进去
        set @i=@i+1  --相当于i++
    end
    
go 
delete from score 
select*from scorebak

drop trigger score_delete
select *from score 

select*from course
insert into score values(1,'3-105',79)
insert into score values(2,'3-105',79)
insert into score values(3,'3-105',79)
insert into score values(4,'3-105',79)
insert into score values(5,'3-105',79)
insert into score values(6,'3-105',79)

--事务:保障整个流程的完整执行,全部没有问题,统一提交,一旦有问题,回到原点.比如银行转账.

begin tran --z事务的开始 --开始写流程语句 --语句写完之后

if @@ERROR>0

rollback tran--回滚事务

else

commit tran--ti提交事务

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