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.
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.
– Reginaldo Rigo
Well, the error is exactly what @Reginaldorigo said. There’s no helping you without more details...
– Jéf Bueno
uhm, maybe passing the ID of the event too. I’ll test
– Jefferson Kist
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..
– Jefferson Kist