CODEWITHSUNDEEP
mysqltriggers
Devisy
Devisy

Reputation: 159

Error when run query trigger in MySQL

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

Upvotes: 0

Views: 44

Answers (2)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

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

Upvotes: 1

Chip Dean
Chip Dean

Reputation: 4302

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!

Upvotes: 1

Related Questions