审批日志添加为评论

一、demo脚本

DROP PROCEDURE IF EXISTS `ldcrm_add_modcomments`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ldcrm_add_modcomments`(
    tmp_reltab VARCHAR(100),
    tmp_related_to INT(19),
    tmp_content TEXT,
    tmp_ownerid INT(19)
)
BEGIN
    
    SET @maxid=(SELECT MAX(crmid) FROM ld_crmentity);
    INSERT INTO ld_crmentity(crmid,setype) SELECT @maxid+1,'ModComments';        

    UPDATE ld_crmentity_seq SET id=(SELECT MAX(crmid) FROM ld_crmentity);    

    INSERT INTO ld_modcomments(
        modcommentsid,
        commentcontent,
        related_to,
        reltab,
        
        smcreatorid,
        createdtime,
        smownerid,
        modifiedtime,
        deleted
    )
    SELECT 
        @maxid+1,
        tmp_content,
        tmp_related_to,
        tmp_reltab,
    
        tmp_ownerid,
        NOW(),
        tmp_ownerid,
        NOW(),
        0;
    
        INSERT INTO ld_modcommentscf(modcommentsid) SELECT @maxid+1;

end
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `ldcrm_add_modcomments_byapprovelogadd`;
DELIMITER ;;
CREATE TRIGGER `ldcrm_add_modcomments_byapprovelogadd` AFTER INSERT ON `ld_approvelog` FOR EACH ROW BEGIN

    SET @mytmp_reltab=(SELECT setype FROM ld_crmentity WHERE crmid=NEW.crmid);

    IF IFNULL(NEW.description,'')<>'' THEN
        CALL ldcrm_add_modcomments(@mytmp_reltab,NEW.crmid,CONCAT('审批意见：',NEW.description),NEW.approved_by);
    END IF;

end
;;
DELIMITER ;

审批日志添加为评论.sql