CODEWITHSUNDEEP
mysqltriggers
Vinny Neves
Vinny Neves

Reputation: 21

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.

Upvotes: 2

Views: 1718

Answers (2)

Helmi Aziz
Helmi Aziz

Reputation: 53

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

Upvotes: 0

juergen d
juergen d

Reputation: 204756

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 ;

Upvotes: 1

Related Questions