Syntax error when creating a MySQL trigger

Question

I have some problems trying to do a trigger.

Here is my MySQL query for the trigger:

delimiter //
CREATE TRIGGER `aggiornaProduzione`
BEFORE UPDATE ON `strutture` FOR EACH ROW
BEGIN
DECLARE temp bigint;
IF ( tipo=1/*mercato*/ AND old.livello <> new.livello )
THEN (
    SELECT round(2*livello*livello + 13.8*livello)
    FROM strutture WHERE tipo=1 AND city=old.city INTO temp;
    WHILE (temp%6<>0) temp=temp+1;
    END WHILE;
    UPDATE strutture SET produzione=temp WHERE city=new.city AND tipo=1;
    )
END IF;
END //

And the error is

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; WHILE (temp%6<>0) temp=temp+1; END WHILE; UPDATE strutture SET ' at line 8

Does someone have an idea why I'm getting this error?

eggyal · Accepted Answer · 2013-10-27 18:36:53Z

2

Remove the parentheses around your THEN clause. Also your WHILE clause is syntactically incorrect:

delimiter //
CREATE TRIGGER `aggiornaProduzione`
BEFORE UPDATE ON `strutture` FOR EACH ROW
BEGIN
DECLARE temp bigint;
IF ( tipo=1/*mercato*/ AND old.livello <> new.livello )
THEN
    SELECT round(2*livello*livello + 13.8*livello)
    FROM strutture WHERE tipo=1 AND city=old.city INTO temp;
    WHILE temp%6<>0 DO
      SET temp=temp+1;
    END WHILE;
    UPDATE strutture SET produzione=temp WHERE city=new.city AND tipo=1;
END IF;
END //

edited Oct 27, 2013 at 18:36

answered Oct 27, 2013 at 18:28

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

4 Comments

Sanci91 Over a year ago

it'sstill not working, same error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'temp=temp+1; END WHILE; UPDATE strutture SET produzione=temp WHERE cit' at line 9

eggyal Over a year ago

@Sanci: Well, for a start that's not the same error. Now you need to look at the WHILE clause: in particular, it should be WHILE temp%6<>0 DO SET temp=temp+1; END WHILE;. See my edit.

Sanci91 Over a year ago

Yep, we're near the solution. Now there's this last error i suppose, because it's on last line: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//' at line 14

Sanci91 Over a year ago

I removed the final // and now it works! Thanky you, you saved my day !

Collectives™ on Stack Overflow

Syntax error when creating a MySQL trigger

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related