MySQL trigger syntax error on update query

Question

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

score 1 · Accepted Answer · answered Aug 07 '13 at 18:23

1

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 ;

answered Aug 07 '13 at 18:23

wchiquito

16,177
2
34
45

Now it's working fine, I thought it wasn't mandatory to use the delimiters and the begin-end block in fact I did not use them in the other database. What is the difference between using them or not? Thanks mate! – user1638466 Aug 07 '13 at 18:45

score 1 · Answer 2 · answered Aug 07 '13 at 18:36

1

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

answered Aug 07 '13 at 18:36

Sylvain Leroux

50,096
7
103
125

thanks! So update and where are considered two different statements? – user1638466 Aug 07 '13 at 18:54
@user1638466 No. `UPDATE ...` is a *statement* having a `WHERE` *clause*. Routhly speaking a statement is terminated by a semi-colon(`;`). Here you have your `UPDATE ... ;` statement and the `IF ... THEN ...END IF ;` flow control statement. – Sylvain Leroux Aug 07 '13 at 19:01
understood, I'm pretty new with triggers as you can see – user1638466 Aug 07 '13 at 19:37

score 1 · Answer 3 · answered Aug 07 '13 at 18:41

1

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)

answered Aug 07 '13 at 18:41

Filipe Silva

21,189
5
53
68

well I just used the other code and it works fine, but what's the real problem? I also tried with only one ";" and it did not work! thank you too! – user1638466 Aug 07 '13 at 18:51
You use delimiters in mysql console when you have multiple statements. See this question for a better explanation: http://stackoverflow.com/q/10259504/1385896 – Filipe Silva Aug 07 '13 at 18:54

MySQL trigger syntax error on update query

3 Answers3