'MySQL trigger, change value before update conditionally

I'm trying to change a value conditionally on my trigger, but I've got an error.

My trigger is: 

CREATE TRIGGER `defineBase` BEFORE INSERT ON `perguntas`
FOR EACH ROW 
BEGIN
    IF NEW.per_base = 0 THEN
        SET NEW.per_base = (SELECT per_id FROM perguntas ORDER BY per_id DESC LIMIT 1) + 1;
    END IF;
END;

but doesn't work.

mysqltriggers


Solution 1:[1]

You need to change the delimiter to something else than ;. Otherwise the trigger definition stops at the first ;

delimiter |
CREATE TRIGGER `defineBase` BEFORE INSERT ON `perguntas`
FOR EACH ROW 
BEGIN
    IF NEW.per_base = 0 THEN
        SET NEW.per_base = (SELECT per_id FROM perguntas ORDER BY per_id DESC LIMIT 1) + 1;
    END IF;
END
|
delimiter ;

Solution 2:[2]

I also have the same problem. My SQL code before is just like this (without delimiter):

CREATE TRIGGER update_created_time BEFORE INSERT
ON contact FOR EACH ROW
BEGIN
    SET NEW.created=NOW();
END

Then, after i add the following DELIMITER // and close it with the same //

DELIMITER //
CREATE TRIGGER update_created_time BEFORE INSERT
ON contact FOR EACH ROW
BEGIN
    SET NEW.created=NOW();
END //

It works. I hope it can help someone in the future...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 juergen d
Solution 2 Helmi Aziz

Related Questions