Error trigger insert into after insert

Question

Hi guys I am trying to make some triggers to update in one table after insert in other table but it reports me an error. Two tables have the same fields and with the same name.

Here is the code.

DELIMITER //

CREATE TRIGGER Insertar_Bares_Private AFTER INSERT ON Bares
FOR EACH ROW
BEGIN
INSERT INTO Bares_Private (nombrebar, direccion) VALUES (new.nombrebar, new.direccion);
END//

CREATE TRIGGER Insertar_Categorias_Private AFTER INSERT ON Categorias
FOR EACH ROW
BEGIN
INSERT INTO Categorias_Private (nombrecategoria) VALUES (new.nombrecategoria);
END//

CREATE TRIGGER Insertar_Productos_Private AFTER INSERT ON Productos
FOR EACH ROW
BEGIN
INSERT INTO Productos_Private (nombreproducto, idcategoria, descripcion, precio, imagen) VALUES (new.nombreproducto, new.idcategoria, new.descripcion, new.precio, new.imagen);
END//

-- Triggers al actualizar

CREATE TRIGGER Actualizar_Usuarios_Private AFTER UPDATE on Usuarios
for each ROW
BEGIN
UPDATE Usuarios_Private
SET nombreusuario=new.nombreusuario, contrasenia=new.contrasenia, email=new.email, telefono=new.telefono
where idusuario=new.idusuario;
END//

CREATE TRIGGER Actualizar_Bares_Private AFTER UPDATE on Bares
for each ROW
BEGIN
UPDATE Bares_Private
SET nombrebar=new.nombrebar, direccion=new.direccion
where idbar=new.idbar;
END//

CREATE TRIGGER Actualizar_Categorias_Private AFTER UPDATE on Categorias
for each ROW
BEGIN
UPDATE Categorias_Private
SET nombrecategoria=new.nombrecategoria
where idcategoria=new.idcategoria;
END//

CREATE TRIGGER Actualizar_Productos_Private AFTER UPDATE on Productos
for each ROW
BEGIN
UPDATE Productos_Private
SET nombreproducto=new.nombreproducto, idcategoria=new.idcategoria, descripcion=new.descripcion, precio=new.precio, imagen=new.imagen
where idproducto=new.idproducto;
END//

DELIMITER ;

And here is the error reported.

#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Thank you in advice.

Have a look on this [post](http://stackoverflow.com/a/19020753/2451726) — Arulkumar, May 23 '16 at 19:31

Arulkumar · Accepted Answer · 2016-05-23T19:24:36.773

2

Remove the ; from all the END; and apply the delimiter END// will solve your problem.

Since you already placed the proper ; for the INSERT/UPDATE statement, so no need to place it again in the END;.

edited May 23 '16 at 19:24

answered May 23 '16 at 17:54

Arulkumar

12,966
14
47
68

do you mean this way? (see edited). It does not work the error changes to this one. #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 'CREATE TRIGGER Insertar_Bares_Private AFTER INSERT ON Bares FOR EACH ROW BEGIN' at line 9 – Gonzalo Benedi May 23 '16 at 18:46
1

Oh, could try to set each block ends with your delimiter `END//` instead of `END;` – Arulkumar May 23 '16 at 18:58

score 0 · Answer 2 · answered May 23 '16 at 19:23

You only need to change the delimiter before defining a trigger if the trigger contains more than one query. This is because you need to use the default delimiter ; in this case to separate the queries within the trigger, but the MySQL client will incorrectly think you mean to terminate the trigger definition here. This also requires you to use BEGIN ... END around the block of queries in the trigger.

DELIMITER //

CREATE TRIGGER Insertar_Usuarios_Private AFTER INSERT ON Usuarios
FOR EACH ROW
BEGIN
INSERT INTO Usuarios_Private (nombreusuario, contrasenia, email,telefono) VALUES (new.nombreusuario, new.contrasenia, new.email, new.telefono);
END//

CREATE TRIGGER Insertar_Bares_Private AFTER INSERT ON Bares
FOR EACH ROW
BEGIN
INSERT INTO Bares_Private (nombrebar, direccion) VALUES (new.nombrebar, new.direccion);
END//

-- and so on ...
DELIMITER ;

However, your triggers each only contain one query. Therefore you can dispense with the delimiter and the BEGIN ... END block entirely.

CREATE TRIGGER Insertar_Usuarios_Private AFTER INSERT ON Usuarios
FOR EACH ROW
INSERT INTO Usuarios_Private (nombreusuario, contrasenia, email,telefono) VALUES (new.nombreusuario, new.contrasenia, new.email, new.telefono);

CREATE TRIGGER Insertar_Bares_Private AFTER INSERT ON Bares
FOR EACH ROW
INSERT INTO Bares_Private (nombrebar, direccion) VALUES (new.nombrebar, new.direccion);

-- and so on ...

Ok, thank you but now is reporting that error (see edited). I am using MySQL 5.5.42 but I do not think that I am using two triggers for the same action time and event. — Gonzalo Benedi, May 23 '16 at 19:34
Have you successfully added one or more of the triggers while testing and now are trying to add the same trigger again? Try `DROP TRIGGER IF EXISTS trigger_name;` for each one of your triggers before running the commands again. — Matt Raines, May 23 '16 at 19:37
It isn't massively helpful to edit your question to ask a completely different question after it has been answered. This means that future visitors who have the same problem won't be able to find it or the answers won't make sense. Better to add an update to the bottom of the question when you edit it and leave the original material intact, I think. — Matt Raines, May 23 '16 at 19:39
Yes men, sorry you are right I will take care the next time. Anyway it worked thank you very much — Gonzalo Benedi, May 25 '16 at 07:48

Error trigger insert into after insert

2 Answers2