修改日志生成提醒

一、demo脚本

DROP TRIGGER IF EXISTS `trigger_after_modtracker_detail_insert`;
DELIMITER ;;
CREATE TRIGGER `trigger_after_modtracker_detail_insert` AFTER INSERT ON `ld_modtracker_detail` FOR EACH ROW 
BEGIN

    SET @tmp_thiscrmid=(SELECT crmid FROM ld_modtracker_basic WHERE id=NEW.id);
    IF EXISTS (SELECT 1 FROM ld_account WHERE accountid=@tmp_thiscrmid) THEN
        SET @remind_users=(SELECT GROUP_CONCAT(id) FROM ld_users WHERE status='Active' AND is_admin='on');
        SET @remind_text=(
            SELECT
                CONCAT(
                    '请注意，',d.last_name,
                    '在',a.changedon,
                    '将客户【',c.account_no,c.accountname,'】字段【',
                    (SELECT x.fieldlabel FROM ld_tranlabel x LEFT JOIN ld_field y ON y.fieldid=x.Relateid AND x.tabid=6 AND x.trantable='ld_field' AND x.languagetype='zh' WHERE y.fieldname=b.fieldname LIMIT 1),
                    '】由 “',IF(IFNULL(b.prevalue,'')='','空值',(CASE WHEN b.fieldname='assigned_user_id' THEN (SELECT last_name FROM ld_users WHERE id=b.prevalue) ELSE b.prevalue END )),
                    '” 修改为 ”',IF(IFNULL(b.postvalue,'')='','空值',(CASE WHEN b.fieldname='assigned_user_id' THEN (SELECT last_name FROM ld_users WHERE id=b.postvalue) ELSE b.postvalue END )),'” '
                )AS text
            FROM ld_modtracker_basic a
            LEFT JOIN ld_modtracker_detail b ON b.id=a.id
            LEFT JOIN ld_account c ON a.crmid=c.accountid
            LEFT JOIN ld_users d ON a.whodid=d.id
            WHERE a.id=NEW.id
        );
    
    CALL ldcrm_add_reminder(@remind_text,@remind_users,'Accounts',@tmp_thiscrmid);
    END IF;

END
;;
DELIMITER ;

修改日志生成提醒.sql