Marcoluz Marcoluz - 4 months ago 10
MySQL Question

Trigger mysql in altervista

i'm using mysql on altervista and i did this trigger but when i try it have "#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 5 ", help pls

CREATE TRIGGER increase
AFTER INSERT ON tableA
FOR EACH ROW
BEGIN
DECLARE counter INTEGER; <---- line 5

SELECT count(*)
INTO counter
FROM tableC;

IF(counter == 0) THEN
UPDATE tableB
SET num = num + 1;
END IF;
END

Answer

You need to change the delimiter - MySQL is seeing the first ";" as the end of the CREATE TRIGGER statement.

Try this:

/* Change the delimiter so we can use ";" within the CREATE TRIGGER */
DELIMITER $$

CREATE TRIGGER increase
BEFORE INSERT ON tableA
FOR EACH ROW BEGIN
     DECLARE counter INTEGER;  

    SELECT count(*)
     INTO counter
     FROM tableC;

     IF(counter ==  0) THEN
        UPDATE tableB
       SET num = num + 1;
     END IF;
END$$
/* This is now "END$$" not "END;" */

/* Reset the delimiter back to ";" */
DELIMITER ;