CODEWITHSUNDEEP
mysqltriggers
user1638466
user1638466

Reputation: 310

MySQL trigger syntax error on update query

I'm trying to create a trigger on MySQL using 2 tables. I'm able to do it in another database but the same code adjusted in another database is giving me syntax error since hours!

CREATE TRIGGER `free_video_used` AFTER UPDATE ON `user`     
FOR EACH ROW
IF new.field = 1 THEN
UPDATE free_video SET free_video.used = 1 WHERE free_video.uid = NEW.uid; 
END IF;

As you can easily understand after an update on the table "user", the other table is modified where the field "uid" is equal. Why isn't it working?! Thanks!!

Upvotes: 1

Views: 182

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

Your code is essentially correct, This is a problem with using multiple ; in the same statement.

Try it like this:

DELIMITER //
    CREATE TRIGGER `free_video_used` AFTER UPDATE ON `user`     
    FOR EACH ROW
    BEGIN
      IF new.field = 1 THEN
          UPDATE free_video SET free_video.used = 1 WHERE free_video.uid = NEW.uid; 
      END IF; 
    END
//

See this fiddle to see it working (notice that on sqlfiddle yo define the delimiter // on the bottom bar)

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

If you have multiple statements in your trigger body, you have to enclose them between BEGIN and END. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html :

By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops

Here you have to write something like:

CREATE TRIGGER `free_video_used` AFTER UPDATE ON `user`     
FOR EACH ROW
BEGIN
IF new.field = 1 THEN
UPDATE free_video SET free_video.used = 1 WHERE free_video.uid = NEW.uid; 
END IF;
END

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

Try:

DELIMITER $$

CREATE TRIGGER `free_video_used` AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
    IF (new.field = 1) THEN
        UPDATE free_video SET free_video.used = 1 WHERE free_video.uid = new.uid;
    END IF;
END$$

DELIMITER ;

Upvotes: 1

Related Questions