脚本2—合同订单上自定义的字段根据负责人自动取值触发器

lingdang

脚本内容：

drop trigger if exists update_ld_salesordercf_lcs1;

delimiter ||

CREATE TRIGGER update_ld_salesordercf_lcs1 BEFORE INSERT ON ld_salesordercf

FOR EACH ROW

BEGIN

set @smownerid=0;

set @cf_1570=NULL; -- 手机

set @cf_1571=NULL; -- 邮箱

set @cf_1572=NULL; -- 身份证号

set @cf_1573=NULL; -- 通讯地址

select smownerid into @smownerid from ld_salesorder where salesorderid= NEW.salesorderid; -- 从主表中找到用户id

select phone_mobile into @cf_1570 from ld_users where id=@smownerid;

select email1 into @cf_1571 from ld_users where id=@smownerid;

select address_postalcode into @cf_1572 from ld_users where id=@smownerid;

select address_street into @cf_1573 from ld_users where id=@smownerid;

set NEW.cf_1570=@cf_1570;

set NEW.cf_1571=@cf_1571;

set NEW.cf_1572=@cf_1572;

set NEW.cf_1573=@cf_1573;

END ||

delimiter;

drop trigger if exists update_ld_salesordercf_lcs2;

delimiter ||

CREATE TRIGGER update_ld_salesordercf_lcs2 AFTER UPDATE ON ld_salesorder

FOR EACH ROW

BEGIN

update ld_salesorder a

left JOIN ld_users b on a.smownerid=b.id

left JOIN ld_salesordercf c on c.salesorderid=a.salesorderid

set c.cf_1570=b.phone_mobile,c.cf_1571=b.email1,c.cf_1572=b.address_postalcode, c.cf_1573=b.address_street where a.salesorderi

d=old.salesorderid;

END ||

delimiter;

drop trigger if exists update_ld_salesordercf_lcs3;

delimiter ||

CREATE TRIGGER update_ld_salesordercf_lcs3 AFTER UPDATE ON ld_users

FOR EACH ROW

BEGIN

update ld_salesorder a

left JOIN ld_users b on a.smownerid=b.id

left JOIN ld_salesordercf c on c.salesorderid=a.salesorderid

set c.cf_1570=b.phone_mobile,c.cf_1571=b.email1,c.cf_1572=b.address_postalcode, c.cf_1573=b.address_street where a.smownerid=old.id;

END ||

delimiter;