包含的触发器内容挺全面的,记录一下
delimiter $$
use `dst_111yao_doms`$$
drop trigger /*!50032 IF EXISTS */ `d_shop_ware_price_update`$$
create
/*!50017 DEFINER = 'dstadmin'@'%' */
trigger `d_shop_ware_price_update` after update on `d_shop_ware`
for each row
begin
#d_price_upt是否包含0或1的状态的记录,如果存在,那么按照同步情况更新这些记录
if exists(select * from `dst_111yao_doms`.`d_price_upt` where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku and `status` = '0'
union
select * from `dst_111yao_doms`.`d_price_upt` where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku and `status` = '1' )
then
#关联查询flag_price
select b.flag_price into @flag_price_copy from `dst_111yao_doms`.`d_shop_ware` as a,`dst_111yao_doms`.`d_platform` as b
where a.pfid = b.pfid and a.olshopid = NEW.olshopid and a.tfid = NEW.pfid and a.warecode = NEW.outter_id and a.skuid = NEW.sku;
#如果flag_price=1那么监控字段d_shop_ware.real_price的变化，有变化就生成实际价的同步任务
if @flag_price_copy = '1' then
if NEW.real_price <> OLD.real_price then
update `dst_111yao_doms`.`d_price_upt` set price = NEW.real_price , `status` = '0' , createtime = now()
where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku;
end if;
#如果flag_price= 2 ,
elseif @flag_price_copy = '2' then
#监控字段d_shop_ware.nor_price的变化,有变化就需要生成正常价的同步任务
if NEW.nor_price <> OLD.nor_price then
update `dst_111yao_doms`.`d_price_upt` set price = NEW.nor_price , `status` = '0' , createtime = now()
where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku;
end if;
#监控字段d_shop_ware.prom_price的变化,有变化并且‘促销价取消标记’ 等于（0未取消）的情况下，就需要生成促销价的同步任务
if (NEW.prom_price <> OLD.prom_price and NEW.flag_cancel = '0') then
update `dst_111yao_doms`.`d_price_upt` set price = NEW.prom_price , `status` = '0' , createtime = now()
where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku;
end if;
#监控字段d_shop_ware.flag_cancel的变化,当‘促销价取消标记’ 从（0未取消）变成（1已取消 ，2已结束）时，需要生成结束促销活动的同步任务,只需要取值实际价就行
if (OLD.flag_cancel = '0' and (NEW.flag_cancel = '1' or NEW.flag_cancel = '2')) then
update `dst_111yao_doms`.`d_price_upt` set price = NEW.real_price , `status` = '0' , createtime = now()
where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku;
end if;
end if;
elseif exists(select * from `dst_111yao_doms`.`d_price_upt` where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku and `status` = '2') then
update `dst_111yao_doms`.`d_price_upt` set update_time = now()
where olshopid = NEW.olshopid and tfid = NEW.pfid and warecode = NEW.outter_id and skuid = NEW.sku;
else
#关联查询flag_price___
select b.flag_price into @flag_price_copy from `dst_111yao_doms`.`d_shop_ware` as a,`dst_111yao_doms`.`d_platform` as b
where a.pfid = b.pfid and a.olshopid = NEW.olshopid and a.tfid = NEW.pfid and a.warecode = NEW.outter_id and a.skuid = NEW.sku;
#如果flag_price=1那么监控字段d_shop_ware.real_price的变化，有变化就生成实际价的同步任务
if @flag_price_copy = '1' then
if NEW.real_price <> OLD.real_price then
insert into `dst_111yao_doms`.`d_price_upt` (olshopid,tfid, busno_online, numiid, warecode, skuid, price, `status`, execdate, createtime)
values (NEW.olshopid , NEW.pfid , (select s.sellerid from d_shop as s where olshopid = NEW.olshopid) , NEW.num_iid , NEW.outter_id , NEW.sku , NEW.real_price , 0 , null , now());
end if;
#如果flag_price=2,
elseif @flag_price_copy = '2' then
#监控字段d_shop_ware.nor_price的变化,有变化就需要生成正常价的同步任务
if NEW.nor_price <> OLD.nor_price then
insert into `dst_111yao_doms`.`d_price_upt` (olshopid,tfid, busno_online, numiid, warecode, skuid, price, `status`, execdate, createtime)
values (NEW.olshopid , NEW.pfid , (select s.sellerid from d_shop as s where olshopid = NEW.olshopid) , NEW.num_iid , NEW.outter_id , NEW.sku , NEW.nor_price , 0 , null , now());
end if;
#监控字段d_shop_ware.prom_price的变化,有变化并且‘促销价取消标记’ 等于（0未取消）的情况下，就需要生成促销价的同步任务
if (NEW.prom_price <> OLD.prom_price and NEW.flag_cancel = '0') then
insert into `dst_111yao_doms`.`d_price_upt` (olshopid,tfid, busno_online, numiid, warecode, skuid, price, `status`, execdate, createtime)
values (NEW.olshopid , NEW.pfid , (select s.sellerid from d_shop as s where olshopid = NEW.olshopid) , NEW.num_iid , NEW.outter_id , NEW.sku , NEW.prom_price , 0 , null , now());
end if;
#监控字段d_shop_ware.flag_cancel的变化,当‘促销价取消标记’ 从（0未取消）变成（1已取消 ，2已结束）时，需要生成结束促销活动的同步任务,只需要取值实际价就行
if (OLD.flag_cancel = '0' and (NEW.flag_cancel = '1' or NEW.flag_cancel = '2')) then
insert into `dst_111yao_doms`.`d_price_upt` (olshopid,tfid, busno_online, numiid, warecode, skuid, price, `status`, execdate, createtime)
values (NEW.olshopid , NEW.pfid , (select s.sellerid from d_shop as s where olshopid = NEW.olshopid) , NEW.num_iid , NEW.outter_id , NEW.sku , NEW.real_price , 0 , null , now());
end if;
end if;
end if;
end;
$$
delimiter ;
