我需要在插入一条记录的时候同时更新相关记录,但是如下的trigger会报错,因为mysql不允许在trigger中update,如何能实现相同的效果?
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `tbl_afterinsert_trigger`$$CREATE
TRIGGER `tbl_afterinsert_trigger` AFTER INSERT ON `tbl`
FOR EACH ROW BEGIN
IF NEW.group_id > 0 THEN
SET @cnt = (SELECT count(*) FROM tbl WHERE is_show = 1 AND group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows);
IF @cnt > 1 THEN
SELECT id INTO @id FROM tbl WHERE is_show = 1 AND group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows ORDER BY created_at DESC LIMIT 1;
UPDATE tbl SET is_show = 0 WHERE group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows AND id <> @id;
END IF;
END IF;
END;
$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `tbl_afterinsert_trigger`$$CREATE
TRIGGER `tbl_afterinsert_trigger` AFTER INSERT ON `tbl`
FOR EACH ROW BEGIN
IF NEW.group_id > 0 THEN
SET @cnt = (SELECT count(*) FROM tbl WHERE is_show = 1 AND group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows);
IF @cnt > 1 THEN
SELECT id INTO @id FROM tbl WHERE is_show = 1 AND group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows ORDER BY created_at DESC LIMIT 1;
UPDATE tbl SET is_show = 0 WHERE group_id = NEW.group_id AND toprank_id = NEW.toprank_id AND time_windows = NEW.time_windows AND id <> @id;
END IF;
END IF;
END;
$$
DELIMITER ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货