通过触发器分发同步数据

通过触发器分发同步数据


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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-2124034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-2124034/