积分触发器 mysql 触发器的使用

将多个数据库中的某列数据同步时需要用到mysql触发器，以下可做参考，已简单测试ok.

//修改红枣的积分时触发

drop trigger if exists u_hongzaocount;
delimiter $$
create trigger u_hongzaocount  after update on 


hongzao.pre_common_member_count
for each row 
begin
 declare v int(10);
 declare vj int(10);
 set v =(select extcredits2 from 


qifangjie.pre_common_member_count where uid=new.uid);
 set vj =(select extcredits2 from 


jifenshop.pre_common_member_count where uid=new.uid);
 if (new.extcredits2>old.extcredits2 and new.extcredits2>v) or 


(new.extcredits2<old.extcredits2 and new.extcredits2<v)  then
update qifangjie.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if;
 if (new.extcredits2>old.extcredits2 and new.extcredits2>vj) 


or (new.extcredits2<old.extcredits2 and new.extcredits2<vj)  


then
update jifenshop.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if;
end$$






//添加红枣的积分记录时触发
drop trigger if exists a_hongzaocount;
delimiter $$
create trigger a_hongzaocount  before insert on 


hongzao.pre_common_member_count
for each row 
begin
 declare v int(10);
 set v =(select extcredits2 from 


qifangjie.pre_common_member_count where uid=new.uid);
 if v>0   then
set new.extcredits2=v;
end if; 
end$$




//修改七纺街的积分时触发
drop trigger if exists u_qifangjiecount;
delimiter $$
create trigger u_qifangjiecount  after update on 


qifangjie.pre_common_member_count
for each row 
begin
 declare v int(10);
 declare vj int(10);
 set v =(select extcredits2 from 


hongzao.pre_common_member_count where uid=new.uid);
 set vj =(select extcredits2 from 


jifenshop.pre_common_member_count where uid=new.uid);
 if (new.extcredits2>old.extcredits2 and new.extcredits2>v) or 


(new.extcredits2<old.extcredits2 and new.extcredits2<v)  then
update hongzao.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if;
 if (new.extcredits2>old.extcredits2 and new.extcredits2>vj) 


or (new.extcredits2<old.extcredits2 and new.extcredits2<vj)  


then
update jifenshop.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if; 
end$$


//添加七纺街的积分记录时触发
drop trigger if exists a_qifangjiecount;
delimiter $$
create trigger a_qifangjiecount  before insert on 


qifangjie.pre_common_member_count
for each row 
begin
 declare v int(10);
 set v =(select extcredits2 from 


hongzao.pre_common_member_count where uid=new.uid);
 if v>0  then
set new.extcredits2=v;
end if; 
end$$




//修改积分商城的积分时触发
drop trigger if exists u_jifenshopcount;
delimiter $$
create trigger u_jifenshopcount  after update on 


jifenshop.pre_common_member_count
for each row 
begin
 declare v int(10);
 declare vj int(10);
 set v =(select extcredits2 from 


hongzao.pre_common_member_count where uid=new.uid);
 set vj =(select extcredits2 from 


qifangjie.pre_common_member_count where uid=new.uid);
 if (new.extcredits2>old.extcredits2 and new.extcredits2>v) or 


(new.extcredits2<old.extcredits2 and new.extcredits2<v)  then
update hongzao.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if;
 if (new.extcredits2>old.extcredits2 and new.extcredits2>vj) 


or (new.extcredits2<old.extcredits2 and new.extcredits2<vj)  


then
update qifangjie.pre_common_member_count set 


extcredits2=new.extcredits2 where uid=new.uid;
end if; 
end$$


//添加积分商城的积分记录时触发
drop trigger if exists a_jifenshopcount;
delimiter $$
create trigger a_jifenshopcount  before insert on 


jifenshop.pre_common_member_count
for each row 
begin
 declare v int(10);
 declare vj int(10);
 set v =(select extcredits2 from 


hongzao.pre_common_member_count where uid=new.uid);
 set vj =(select extcredits2 from 


qifangjie.pre_common_member_count where uid=new.uid);
 if v>=vj  then
set new.extcredits2=v; 
 elseif v<vj then
    set new.extcredits2=vj;
end if; 
end$$