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