How to make an assignment like "a=a+1" in a mySQL trigger with multiple tables?

Question

Using MySQL, I would like to add values from a table in an empty table using a trigger.

So I made two triggers, but I have a problem in the assignment of the value, in the second one.

First, I made this trigger initialicing the values of the fields I would like to change after with the other trigger.

DELIMITER $$
CREATE TRIGGER addClavesClasificacion 
BEFORE INSERT ON partido FOR EACH ROW
BEGIN
    DECLARE idEquipo_clasificacion VARCHAR(20);
    DECLARE idLiga_clasificacion, idJornada_clasificacion INT;

    INSERT INTO clasificacion(puntos,nPartidosGF,nPartidosGC,idLiga_clasificacion,
    idJornada_clasificacion,idEquipo_clasificacion) 
    VALUES (0,0,0,NEW.idLiga_partido,NEW.idJornada_partido,NEW.idEquipoLocal);

    INSERT INTO clasificacion(puntos,nPartidosGF,nPartidosGC,idLiga_clasificacion,
    idJornada_clasificacion, idEquipo_clasificacion) 
    VALUES (0,0,0,NEW.idLiga_partido,NEW.idJornada_partido,NEW.idEquipoVisitante);
END;
$$

That is the trigger of the problem, the problem is in the (UPDATE clasificacion SET puntos=puntos+1...). There is no error but when INSERT some data in partido and look for the result in clasificacion of the assgiment of puntos=puntos+1 and the result is a null value in the field of the row that it has to change.

DELIMITER $$
CREATE TRIGGER addclasificacion 
AFTER INSERT ON partido FOR EACH ROW
BEGIN
    DECLARE nPartidosGC, nPartidosGF, puntos INT;

    SELECT nPartidosGC INTO nPartidosGC FROM clasificacion 
    WHERE NEW.idLiga_partido=idLiga_clasificacion 
    AND NEW.idEquipoLocal=idEquipo_clasificacion 
    AND  NEW.idJornada_partido=idJornada_clasificacion;

    SELECT nPartidosGF INTO nPartidosGF FROM clasificacion 
    WHERE NEW.idLiga_partido=idLiga_clasificacion 
    AND NEW.idEquipoLocal=idEquipo_clasificacion 
    AND NEW.idJornada_partido=idJornada_clasificacion;

    SELECT puntos INTO puntos FROM clasificacion 
    WHERE NEW.idLiga_partido=idLiga_clasificacion 
    AND NEW.idEquipoLocal=idEquipo_clasificacion 
    AND NEW.idJornada_partido=idJornada_clasificacion;

    UPDATE clasificacion SET puntos=puntos+1
    WHERE NEW.golesLocal=NEW.golesVisitante 
    AND NEW.idEquipoVisitante=idEquipo_clasificacion 
    AND NEW.idLiga_partido=iDLiga_clasificacion 
    AND NEW.idJornada_partido=idJornada_clasificacion;  
END;
$$

Is there any way to assign a value that way?

*I tried the assignmentent (UPDATE clasificacion SET puntos=puntos+1) in a concrete row outside the trigger an it works perfectly.

I supose the error is on the (select puntos ..) in the second trigger, but I don't know for sure and I can't solve it

Don't forget to upvote, downvote, accept any answers with the Green checkmark whose ever it is if appropriate. That is our feedback for our efforts. — Drew, Jul 03 '16 at 23:29

score 0 · Accepted Answer · answered Aug 30 '15 at 22:02

I realise that it is not necesary to declare the variables to change there value, that is the solution that works.

DELIMITER $$
CREATE TRIGGER addclasificacion 
AFTER INSERT ON partido FOR EACH ROW
BEGIN
    UPDATE clasificacion SET puntos=puntos+1
    WHERE NEW.golesLocal=NEW.golesVisitante 
    AND (NEW.idEquipoVisitante=idEquipo_clasificacion 
    OR NEW.idEquipoLocal=idEquipo_clasificacion)
    AND NEW.idLiga_partido=iDLiga_clasificacion 
    AND NEW.idJornada_partido=idJornada_clasificacion;  
END;
$$

How to make an assignment like "a=a+1" in a mySQL trigger with multiple tables?

1 Answers1