56,679
社区成员
发帖
与我相关
我的任务
分享
drop table if exists ttab1,ttab2;
CREATE TABLE TTab1(ID INT UNIQUE,Name NVARCHAR(50));
CREATE TABLE TTab2(ID INT UNIQUE,Name NVARCHAR(50));
truncate table TTab1;
truncate table TTab2;
drop trigger if exists tr_TTab1;
drop trigger if exists tr_TTab2;
delimiter ||
create trigger tr_TTab1 after insert on TTab1
for each row
begin
set @disable=1;
if @disable=1 and NOT EXISTS(SELECT 1 FROM ttab2 where ID=new.ID) then
insert into TTab2(ID,Name) values(new.ID,new.Name);
end if;
set @disable=0;
end||
create trigger tr_TTab2 after insert on TTab2
for each row
begin
set @disable=1;
if @disable=1 and NOT EXISTS(SELECT 1 FROM ttab1 where ID=new.ID) then
insert into TTab1(ID,Name) values(new.ID,new.Name);
end if;
set @disable=0;
end||
delimiter ;
#测试新增TTab1
INSERT INTO TTab1
( ID, Name )
VALUES ( 1, -- ID - int
'Roy1' -- Name - nvarchar(50)
);
#测试新增TTab2
INSERT INTO TTab2
( ID, Name )
VALUES ( 2, -- ID - int
'Roy1' -- Name - nvarchar(50)
);
select ID,Name from TTab1;
select ID,Name from TTab2;