I have the following trigger:
CREATE TRIGGER actualizarConsulta
AFTER INSERT
ON ranking
FOR EACH ROW
BEGIN
DELETE FROM Consulta C
WHERE C.jugador_id = NEW.player_id;
END;
When I try to execute it I get the following error:
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 'C WHERE C. player_id = NEW.player_id; END 'at line 6
The problem is that you have BEGIN END around a non-delimited declaration with ; . However, if you put the missing element, you must enter a metadata delimiter for the entire definition, because if it does not, the inside will break it.
Therefore, put a ; at the end of the declaration DELETE and place the DELIMITER before the definition of the trigger:
In your where clause change NEW.player_id; to OLD.player_id;
DELIMITER $$
CREATE
TRIGGER actualizarConsulta
AFTER INSERT ON 'ranking'
FOR EACH ROW
BEGIN
DELETE FROM Consulta C
WHERE (C.jugador_id = OLD.player_id);
END$$
OR Modify your Where by adding () and leaving the NEW.player_id; as it is:
DELIMITER $$
CREATE
TRIGGER actualizarConsulta
AFTER INSERT ON 'ranking'
FOR EACH ROW
BEGIN
DELETE FROM Consulta C
WHERE (C.jugador_id = NEW.player_id);
END$$
I apologize if I make an error, I'm on the phone, Regards!