Database row wont update when my TRIGGER fires. — “#1242 – Subquery returns more than 1 row”

I want to insert a new row on table ‘b’ when a column in table ‘a’ is updated. But when the trigger fires with UPDATE REQ SET status='approved' WHERE idReq=1; it throws an error saying:

“#1242 – Subquery returns more than 1 row”

Note: I am using MariaDB 🙂

Here is my TRIGGER:

DELIMITER $$
CREATE TRIGGER asignarNotificacionReqEstatus
AFTER UPDATE ON REQ
FOR EACH ROW
BEGIN
    SET @idReq = (SELECT idReq FROM REQ);
    SET @idPetitioner = (SELECT petitioner FROM REQ);

    IF OLD.status <> NEW.status THEN
        INSERT INTO NOTIFICATION(dayTime, type, content)
        VALUES (NOW(), 'requestAcepted', 
            CONCAT("{",
            "'idReq' : '", @idReq, "', ",
            "'petitioner' : '", @idPetitioner, "', ",
            "}")
        );
    END IF;
END $$
DELIMITER ;

Answer

My assumption reading your code is that your first statement is trying to grab every idReq in the entire REQ table and stick it in the @idReq variable, hence the error that you are getting.

You are already using OLD and NEW later on, so you know about those concepts. I would try not even using the variables and just using NEW.idReq and NEW.petitioner in the INSERT block.

(I haven’t used mariadb, so my answer is based on general sql knowledge and quick internet search.)