mysql触发器案例

墨蓝 2022-09-10 07:20 212阅读 0赞

包含的触发器内容挺全面的,记录一下

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 ;