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;