ERROR 1242 (21000): Subquery Returns more than 1 Row

Question

I have a problem with a Trigger (Mysql).

I tried to build it myself.

The need: I have a system of occurrences where when moving to the closing stage (Stage 4) it check if a description has been typed in the field descricao_encerrado.

Before applying the Trigger need to check if the occurrence is in the Stage 3 (pre-closure level), look 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 while giving the update of Stage 3 to 4 (closed):

ERROR 1242 (21000): Subquery Returns more than 1 Row

I cannot do this control via code, it is a prerequisite of the teacher who is via Trigger.

Answer 1

Your select is returning more than one expensive line Voce can solve like this

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 that is being updated with old values, and even happening with NEW, but NEW contains the new data.