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

Asked

Viewed 1,444 times

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 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.

  • 1

    He is saying that SELECT Stage FROM occurrences WHERE OLD.Stage = 3 is returning more than one line. Therefore not to compare whether it is equal to 3 or not.

  • Well, the error is exactly what @Reginaldorigo said. There’s no helping you without more details...

  • uhm, maybe passing the ID of the event too. I’ll test

  • How would I do if I wanted to compare the ID of the occurrence I’m updating with the id of the bank occurrences ? I don’t know if I expressed myself right..

1 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.

  • Caraca you are too much!! thanks guys the @Wpbarcelos solution worked perfectly!!!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.