不能设置触发变量(Cannot set variable in trigger)

我想设置一个变量在触发器是在表中的最新的条目。 然而，的phpMyAdmin指出存在的线错误SET clicked_campaign_id = 。 我没有看到这里的问题。

CREATE TRIGGER tr_user_action_click 
AFTER INSERT ON users_click FOR EACH ROW
BEGIN
    DECLARE clicked_campaign_id int

    SET clicked_campaign_id = 
    (SELECT campaignId 
    FROM users_click 
    WHERE id = (SELECT max(id) FROM users_click));

    Update onlineportal.`campaigns`
    SET `clicks` = `clicks` + 1
    WHERE id = clicked_campaign_id;
END

是否有不同的方式来设置一个变量...？

Answer 1:

您必须设置在phpMyAdmin的分隔符，如显示的图像：

然后，创建触发器：

CREATE TRIGGER `tr_user_action_click` AFTER INSERT ON `users_click`
FOR EACH ROW
BEGIN
    DECLARE `clicked_campaign_id` INT;

    SET `clicked_campaign_id` = 
    (SELECT `campaignId` 
    FROM `users_click`
    WHERE `id` = (SELECT max(`id`) FROM `users_click`));

    UPDATE `onlineportal`.`campaigns`
    SET `clicks` = `clicks` + 1
    WHERE `id` = `clicked_campaign_id`;
END//

文章来源: Cannot set variable in trigger