Error when run query trigger in MySQL

Question

please help i have trigger code below

DELIMITER $$
CREATE TRIGGER `mydb`.`table0`
AFTER INSERT ON `mydb`.`table0`
FOR EACH ROW
BEGIN    
IF (
    SELECT table1.idtable1  FROM table2, table1, table 
    WHERE table1.idtable1=table2.idtable2
    and table0.idtable0=table1.idtable1
)
THEN
    UPDATE targettable 
    SET targettable.column = 1
    WHERE targettable.idtable=table1.idtable1;
END IF;

END$$

after running it, it shows 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 '' at line 17

how to fix it? MySQL version is 5.5.34

SELECT table1.idtable1 FROM table2, table1, table <- should the last table be table0? — user2182349
– user2182349, Commented Apr 26, 2015 at 18:23

Chip Dean · Accepted Answer · 2015-04-26 18:28:38Z

1

table is a reserved word in mysql. Surround table with backticks. Also you were missing delimiter and a semicolon at the end. This works:

DELIMITER $$
CREATE TRIGGER `mydb`.`table0`
AFTER INSERT ON `mydb`.`table0`
FOR EACH ROW
BEGIN    
IF (
    SELECT table1.idtable1  FROM table2, table1, `table` 
    WHERE table1.idtable1=table2.idtable2
    and table0.idtable0=table1.idtable1
)
THEN
    UPDATE targettable 
    SET targettable.column = 1
    WHERE targettable.idtable=table1.idtable1;
END IF;
END;$$
delimiter ;

I hope that helps!

answered Apr 26, 2015 at 18:28

Chip Dean

4,3023 gold badges27 silver badges38 bronze badges

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

Comments

Abhishekh Gupta · Accepted Answer · 2015-04-26 18:29:24Z

1

DELIMITER $$
CREATE TRIGGER `mydb`.`table0`
AFTER INSERT ON `mydb`.`table0`
FOR EACH ROW
BEGIN    
IF (
    SELECT table1.idtable1  FROM table2, table1, table <-- you should escape this using backticks as this is a reserved word i.e., `table`
    WHERE table1.idtable1=table2.idtable2
    and table0.idtable0=table1.idtable1
)
THEN
    UPDATE targettable 
    SET targettable.column = 1
    WHERE targettable.idtable=table1.idtable1;
END IF;

END$$

answered Apr 26, 2015 at 18:29

Abhishekh Gupta

6,2464 gold badges20 silver badges49 bronze badges

Collectives™ on Stack Overflow

Error when run query trigger in MySQL

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 17

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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 17

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related