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

Question

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

Navigation

#1 by (1 votes)

0

I have a problem with a Trigger (MySQL).

I tried to build it myself.

The need: I have a system of occurrences where when passing to the stage of closing (stage 4) it verifies that a description was entered in the field descricao_encerrado .

Before applying to trigger I need to check if the instance is in stage 3 (pre-closure level), see what I've done so far:

DELIMITER //
CREATE trigger VERIFICAENCERRADA BEFORE UPDATE ON ocorrencias
FOR EACH ROW
BEGIN
SET @descricao_resolvida = NEW.descricao_resolvida;
IF ((SELECT stage FROM ocorrencias WHERE OLD.stage = 3) AND  (CHAR_LENGTH(@descricao_resolvida) < 1) ) 
THEN 
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Deve-se digitar uma descrição no encerramento.';
END IF;
END //

But it is returning me the following error when giving the update of stage 3 to 4 (closed):

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

I can not do this control via code, it is a prerequisite of the teacher that is trigger .

mysql trigger

asked by anonymous 06.12.2016 / 14:26

1 answer

APi $ HTTP For MySQL Database Requests Remove the crossroads.js sharpen (#) and run without it, how do?

score 1 · Accepted Answer

Your select is returning more than one expensive line you can solve this way

DELIMITER //
CREATE trigger VERIFICAENCERRADA BEFORE UPDATE ON ocorrencias
FOR EACH ROW
BEGIN
SET @descricao_resolvida = NEW.descricao_resolvida;
IF ((OLD.stage = 3) AND  (CHAR_LENGTH(@descricao_resolvida) < 1) ) 
THEN 
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Deve-se digitar uma descrição no encerramento.';
END IF;
END //

OLD is referring to the line being updated with old values, and even with NEW, but NEW contains the new data.