Cannot set variable in trigger

问题:

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...?

回答1:

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

Then, create the trigger:

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