mysql - 需要帮助定制 mysql 触发器

Question

这是我当前的触发器:

DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `unique_visit_new_campaign` AFTER INSERT ON `unique_visit` FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pixel_id int;
    DECLARE campaign_id varchar(45);
    DECLARE cur CURSOR FOR SELECT id FROM pixels;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
            ins_loop: LOOP
                FETCH cur INTO pixel_id;
                IF done THEN
                    LEAVE ins_loop;
                END IF;
                INSERT IGNORE INTO pixels_campaign (campaign_id , pixel_id , date) VALUES (NEW.campaign_id ,pixel_id, current_timestamp);
            END LOOP;
        CLOSE cur;
END

当 new.campaign_id 为空或等于字符串 {campaign_id} 时，我需要它不触发

我尝试使用 MySQL IF 但没有成功。

此外，即使已经存在广告事件 ID(当它忽略时)，它也会自动递增。有什么办法可以阻止这种情况发生吗？

Answer 1

你尝试过这个吗？

DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `unique_visit_new_campaign` AFTER INSERT ON `unique_visit` FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pixel_id int;
    DECLARE campaign_id varchar(45);
    DECLARE cur CURSOR FOR SELECT id FROM pixels;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    IF new.campaign = '' or new.campaign = '{campaign_id}' or new.campaign is null THEN

        OPEN cur;
                ins_loop: LOOP
                    FETCH cur INTO pixel_id;
                    IF done THEN
                        LEAVE ins_loop;
                    END IF;
                    INSERT IGNORE INTO pixels_campaign (campaign_id , pixel_id , date) VALUES (NEW.campaign_id ,pixel_id, current_timestamp);
                END LOOP;
            CLOSE cur;
    END IF

END