通过触发器分发同步数据
create table tab(id int not null primary key,name varchar(20),age int,address varchar(200));
create table tab0(id int not null primary key,name varchar(20),age int,address varchar(200));
create table tab1(id int not null primary key,name varchar(20),age int,address varchar(200));
create table tab2(id int not null primary key,name varchar(20),age int,address varchar(200));
insert into tab values(1,’chen’,30,’shanghai’);
insert into tab values(2,’jiang’,40,’shanghai’);
insert into tab values(3,’wang’,31,’shanghai’);
delimiter //
create trigger tri_sync_insert
after insert on tab
for each row
begin
declare
v_result int;
set v_result=mod(new.id,3);
if v_result =0 then
insert into tab0(id,name,age,address) values(new.id,new.name,new.age,new.address);
elseif v_result = 1 then
insert into tab1(id,name,age,address) values(new.id,new.name,new.age,new.address);
else
insert into tab2(id,name,age,address) values(new.id,new.name,new.age,new.address);
end if;
end;
//
delimiter ;
delimiter //
create trigger tri_sync_update
after update on tab
for each row
begin
declare
v_result int;
set v_result=mod(new.id,3);
if v_result =0 then
replace into tab0(id,name,age,address) values(new.id,new.name,new.age,new.address);
elseif v_result = 1 then
replace into tab1(id,name,age,address) values(new.id,new.name,new.age,new.address);
else
replace into tab2(id,name,age,address) values(new.id,new.name,new.age,new.address);
end if;
end;
//
delimiter ;
delimiter //
create trigger tri_sync_delete
after delete on tab
for each row
begin
declare
v_result int;
set v_result=mod(old.id,3);
if v_result =0 then
delete from tab0 where id=old.id;
elseif v_result = 1 then
delete from tab1 where id=old.id;
else
delete from tab2 where id=old.id;
end if;
end;
//
delimiter ;
