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

wchiquito · Accepted Answer · 2013-08-07 18:23:43Z

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 7, 2013 at 18:23

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1638466 Over a year ago

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!

Sylvain Leroux · Accepted Answer · 2013-08-07 18:36:27Z

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 7, 2013 at 18:36

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

3 Comments

user1638466 Over a year ago

thanks! So update and where are considered two different statements?

Sylvain Leroux Over a year ago

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

user1638466 Over a year ago

understood, I'm pretty new with triggers as you can see

Filipe Silva · Accepted Answer · 2013-08-07 18:41:17Z

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 7, 2013 at 18:41

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

2 Comments

user1638466 Over a year ago

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!

Filipe Silva Over a year ago

You use delimiters in mysql console when you have multiple statements. See this question for a better explanation: stackoverflow.com/q/10259504/1385896

Collectives™ on Stack Overflow

MySQL trigger syntax error on update query

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related