Home What is the issue with this trigger (for the right syntax to use near '' )

# What is the issue with this trigger (for the right syntax to use near '' )

Amine Choukri
1#
Amine Choukri Published in 2018-01-12 21:06:21Z
 Please I did this trigger to update table rating after each insert when 2 columns (id_prof,id_etud) inserted are already in the table but it gives mi this error > #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6  The trigger : CREATE TRIGGER Before_Insert_Rate BEFORE INSERT ON rating FOR EACH ROW BEGIN IF (EXISTS(SELECT * FROM rating WHERE id_prof=NEW.id_prof and id_etud = NEW.id_etud)) THEN UPDATE rating SET rate = NEW.rate WHERE id_prof = NEW.id_prof and id_etud = NEW.id_etud; ELSE INSERT INTO rating VALUES (NEW.idprof,New.rate,New.id_etud); END IF END DELIMITER ; 
Bernd Buffen
2#
Bernd Buffen Reply to 2018-01-12 21:14:05Z
 You must set the DELIMITER before. Change it to: DELIMITER // CREATE TRIGGER Before_Insert_Rate BEFORE INSERT ON rating FOR EACH ROW BEGIN IF (EXISTS(SELECT * FROM rating WHERE id_prof=NEW.id_prof and id_etud = NEW.id_etud)) THEN UPDATE rating SET rate = NEW.rate WHERE id_prof = NEW.id_prof and id_etud = NEW.id_etud; ELSE INSERT INTO rating VALUES (NEW.idprof,New.rate,New.id_etud); END IF END; // DELIMITER ; 
Uueerdo
3#
 from the official reference manual. A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. This means no query on table X can INSERT, UPDATE, or DELETE from table X; it also means that if a trigger on table X modifies table Y, it will fail for any query using both table X and Y that "triggers" it. Example: UPDATE x INNER JOIN y ON x.id = y.id SET x.something = 1, y.something = 2 will cause a BEFORE UPDATE ON x trigger (that updates, inserts, or deletes from y) to fail.
 I solved it using ON DUPLICATE KEY on a query instead of trigger CREATE TABLE rating ( id_prof int(11) NOT NULL, rate float NOT NULL, id_etud int(11) NOT NULL, UNIQUE (id_prof,id_etud) )  the query : INSERT INTO rating (id_prof,rate,id_etud) VALUES (1,2,5) ON DUPLICATE KEY UPDATE rate=2