触发器---一张表更新另一张表同步更新

if (object_id('ggaogqa', 'TR') is not null)
    drop trigger ggaogqa
go-----判断触发器是否存在，如存在删除

create  trigger ggaogqa on kexin..test2---创建触发器

after insert,update,delete---------功能：插入、更新、删除
as
   
begin
    declare @inserted int, @deleted int
    select @inserted=COUNT(*) from inserted
    select @deleted=COUNT(*) from deleted
    if @inserted>0 and @deleted=0-----插入记录
    begin
         insert into  gkx..test2(id,name,tot,tot2) select id,name,tot,tot2 from inserted
    end
    else if @inserted>0 and @deleted>0----更新记录
    
begin  
 DELETE FROM gkx..test2 WHERE id = (SELECT id FROM DELETED)
              insert into  gkx..test2(id,name,tot,tot2) select id,name,tot,tot2 from INSERTED
end
    else if @inserted=0 and @deleted>0----删除记录
    begin  
         delete  from gkx..test2 
         where  id=(select   id  from   deleted)
         
    end
end
go




select * from gkx..test2
select * from kexin..test2






select * from gkx..test2
select * from kexin..test2
truncate table kexin..test2
truncate table gkx..test2


insert into kexin..test2(name,tot,tot2) values('ga3','20','50')
insert into kexin..test2(name,tot,tot2) values('ga5','20','50')
insert into kexin..test2(name,tot,tot2) values('ga6','20','50')
insert into kexin..test2(name,tot,tot2) values('ga7','20','50')




update  kexin..test2  
set name='gaokexin'
where id='3'


delete kexin..test2 where id=1