The subquery returned more than one row

Question

The subquery returned more than one row

Navigation

#1 by (2 votes)

0

Hello, I have this problem with a query

DELIMITER //
CREATE TRIGGER pagos AFTER INSERT ON Pagos
FOR EACH ROW
BEGIN
SET @puesto = (SELECT e.puesto FROM Empleado AS e, Pagos AS p WHERE 
e.idEmpleado=p.Empleado_idEmpleado);
IF @puesto='Administrador' THEN
CALL Administrador(new.Empleado_idEmpleado);
END IF;
IF @puesto='Gerente administrativo' THEN
CALL Gerente_administrativo(new.Empleado_idEmpleado);
END IF;
IF @puesto='Coordinador' THEN
CALL Coordinador(new.Empleado_idEmpleado);
END IF;
IF @puesto='Jefaturas' THEN
CALL Jefaturas(new.Empleado_idEmpleado);
END IF;
IF @puesto='Secretarias' THEN
CALL Secretarias(new.Empleado_idEmpleado);
END IF;
IF @puesto='Intendente' THEN
CALL Intendente(new.Empleado_idEmpleado);
END IF;
END//
DELIMITER ;

I have this trigger with which I want to evaluate the position of the employees who are receiving a payment. The problem is that when I insert the ids of the employees in the payment table, it sends me an error when doing the query inside the trigger:

ERROR 1242 (21000): Subquery returns more than 1 row

mysql

asked by Jose Rodolfo De Loza Esquivias 19.11.2018 в 21:21

source

1 answer

First letter of a word in C send checkbox checked by ajax method

score 2 · Answer 1

When you run

SELECT e.puesto FROM Empleado AS e, Pagos AS p WHERE 
e.idEmpleado=p.Empleado_idEmpleado

how many records do you get? Note that you are not limiting it in the WHERE with any variables inside the trigger, so you're always getting the full list of posts in the Empleado table for all the posts with Pagos . And, since you expect a unique value that you want to assign to the variable, the error that shows it will be displayed.

Try to limit the query by using something like WHERE... AND new.Empleado_idEmpleado = p.Empleado_idEmpleado to be related to the record that you just created in the table and make sense of its execution within the trigger.