在两个表中加一个标志字段flag(1:表示不是另一个表触发,0:是由于另一边触发引起),设置默认值为0(表示人工操作,不是触发引起)
alter table ta add flag int default(0)
举例
create trigger ti_ta on ta
for insert
as
begin
if flag=0 --人工
insert into tb values(...)
else
end
goend
alter table ta add flag int default(0)
举例
create trigger ti_ta on ta
for insert
as
begin
if flag=0 --人工
insert into tb values(...)
else
end
goend
create trigger ti_ta on ta
for insert
as
begin
declar @bh varchar(10),@mc varchar(20)
select @bh=bh,@mc=mc from inserted
select @
if flag=0 --人工
insert into tb values(@bh,@mc,1) --注意此时flag=1
end
go
--对B表也这样写,就不会死循环了,且数据同步
create trigger ti_tb on tb
for insert
as
begin
declar @bh varchar(10),@mc varchar(20)
select @bh=bh,@mc=mc from inserted
select @
if flag=0 --人工
insert into ta values(@bh,@mc,1) --注意此时flag=1
end
go
create trigger ti_ta on ta
for insert
as
begin
declar @bh varchar(10),@mc varchar(20)
select @bh=bh,@mc=mc from inserted
if flag=0 --人工
insert into tb values(@bh,@mc,1) --注意此时flag=1
end
go
--对B表也这样写,就不会死循环了,且数据同步
create trigger ti_tb on tb
for insert
as
begin
declar @bh varchar(10),@mc varchar(20)
select @bh=bh,@mc=mc from inserted
if flag=0 --人工
insert into ta values(@bh,@mc,1) --注意此时flag=1
end
go
先在表中加个标志列flag,默认值0,表示是否是触发器触发的标志下面是完整的触发器:(insert)(update,delete触发器类似)
--A触发器:
use a
go
create trigger tr on table1
for insert
as
if exists(select * from inserted where flag=0)
insert into B.dbo.table1(bh,mc,flag)
select bh,mc,1 from inserted where flag=0
go
--B触发器:
use b
go
drop trigger tr
go
create trigger tr on table1
for insert
as
if exists(select * from inserted where flag=0)
insert into A.dbo.table1(bh,mc,flag)
select bh,mc,1 from inserted where flag=0
go
--A触发器:
create trigger tr on table1
for insert,update,delete
as
if not exists(select * from deleted)
begin
if exists(select * from inserted where flag=0)
insert into B.dbo.table1(bh,mc,flag)
select bh,mc,1 from inserted where flag=0
end
else
if not exists(select * from inserted)
begin
if exists(select * from deleted)
delete from B.dbo.table1 where bh in(select bh from deleted)
end
else
if exists(select * from inserted)
update B.dbo.table1 set mc=inserted.mc from inserted where B.dbo.table1.bh=inserted.bh and B.dbo.table1.mc<>inserted.mc
go
--B触发器:
create trigger tr on table1
for insert,update,delete
as
if not exists(select * from deleted)
begin
if exists(select * from inserted where flag=0)
insert into A.dbo.table1(bh,mc,flag)
select bh,mc,1 from inserted where flag=0
end
else
if not exists(select * from inserted)
begin
if exists(select * from deleted)
delete from A.dbo.table1 where bh in(select bh from deleted)
end
else
if exists(select * from inserted)
update A.dbo.table1 set mc=inserted.mc from inserted where A.dbo.table1.bh=inserted.bh and A.dbo.table1.mc<>inserted.mc
go