MySQL插入记录触发器【插入记录后进行其他记录操作】

MySQL插入记录触发器【插入记录后进行其他记录操作】

示例代码如下：

CREATE DEFINER=`root`@`%` TRIGGER `apk_factory`.`download_detail_report_insert_trigger` 
   AFTER INSERT ON apk_factory.download_log FOR EACH ROW
BEGIN
   DECLARE var_parent_id           bigint(11);
   DECLARE var_apk_name            varchar(30);
   DECLARE var_apk_id              bigint(11);
   DECLARE var_recommend_user_id   bigint(11);
   DECLARE var_business_name       varchar(30);
   DECLARE var_business_id         bigint(11);
   DECLARE var_business_account    varchar(30);
   DECLARE var_creator             varchar(30);
   DECLARE var_creator_id          varchar(30);
   DECLARE var_u_account           varchar(30);
   DECLARE var_u_id                bigint(11);
   DECLARE var_recommend_mobile    varchar(30);
   DECLARE var_belong_area_id      bigint(11);
   DECLARE var_belong_area         varchar(30);
   DECLARE var_create_time         timestamp;
   DECLARE var_download_time       timestamp;

   SELECT apk.apk_name
     INTO var_apk_name
     FROM apk
    WHERE apk.id = NEW.apk_id
    LIMIT 0, 1;

   SET var_apk_id = NEW.apk_id;

   SELECT t1.recommend_user_id,
          t1.business_name,
          t1.account,
          t1.parent_id
     INTO var_recommend_user_id,
          var_business_name,
          var_business_account,
          var_parent_id
     FROM business t1
    WHERE t1.id = NEW.business_id
    LIMIT 0, 1;

   SET var_business_id = NEW.business_id;

   SELECT creator_id, recommend_mobile, create_time
     INTO var_creator_id, var_recommend_mobile, var_create_time
     FROM recommend_log
    WHERE id = NEW.recommend_id
    LIMIT 0, 1;

   SELECT t1.id, t1.`name`, t1.account
     INTO var_u_id, var_creator, var_u_account
     FROM `user` t1
    WHERE t1.id = var_creator_id
    LIMIT 0, 1;

   SELECT area.id, area.`name`
     INTO var_belong_area_id, var_belong_area
     FROM tbl_clerk clerk LEFT JOIN tbl_area area ON clerk.area_id = area.id
    WHERE clerk.id = var_recommend_user_id
    LIMIT 0, 1;

   SET var_download_time = NEW.create_time;



   INSERT INTO tbl_down_detail_report(parent_id,
                                      apk_name,
                                      apk_id,
                                      recommend_user_id,
                                      business_name,
                                      business_id,
                                      business_account,
                                      creator,
                                      creator_id,
                                      u_id,
                                      recommend_mobile,
                                      belong_area_id,
                                      belong_area,
                                      create_time,
                                      download_time)
   VALUES (var_parent_id,
           var_apk_name,
           var_apk_id,
           var_recommend_user_id,
           var_business_name,
           var_business_id,
           var_business_account,
           var_creator,
           var_u_account,
           var_u_id,
           var_recommend_mobile,
           var_belong_area_id,
           var_belong_area,
           var_create_time,
           var_download_time);
END;