无法在触发器中设置变量 [英] Cannot set variable in trigger

问题描述

我正在尝试在触发器中设置一个变量，该触发器是表中的最新条目。但是，PHPMyAdmin注意到在 SET clicked_campaign_id = 行有错误。我在这里看不到问题。

I am trying to set a variable in a trigger that is the most recent entry in the table. However, PHPMyAdmin notes there is an error at line SET clicked_campaign_id =. I do not see the problem here.

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

是否有其他方法来设置变量...？

Is there a different way to set a variable...?

推荐答案

您必须在phpMyAdmin中设置DELIMITER，如图所示：

You must set the DELIMITER in phpMyAdmin, as shown in the image:

然后，创建触发器：

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//

这篇关于无法在触发器中设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！