CODEWITHSUNDEEP
mysqlsqltriggers
le0nicolas
le0nicolas

Reputation: 83

MYSQL - How to create a trigger after an UPDATE in a table, to increment +1 the value of a column from another table?

These are my tables:

I'm creating the trigger after i update this table:

Table 'avisos':

idAvisos | retirado 
---------------------
   1     |    0 
   2     |    1

Table 'recolectores':

idRecolector | num_avisos_retirados
-----------------------------------
      1      |         0 
      2      |         4

So what i want to do is, after i update the table avisos to set "retirado" to true (1), i want to sum +1 to the column "num_avisos_retirados" of the recolectores table.

Example: when idAvisos (1) changes "retirado" to 1, the idRecolector (1) will have to change the num_avisos_retirados to 1.

Here's what i've done so far:

CREATE TRIGGER NumAvisosRecolectados_AU #after update
AFTER UPDATE ON avisos.retirado
    IF (NEW.retirado = 1) THEN
        UPDATE recolectores
        SET recolectores.num_avisos_recolectados = recolectores.num_avisos_recolectados + 1
    END IF;

Also, what i don't understand is if i have to use a WHERE clause, to actually hit the right idRecolector of the "recolectores" table. My teacher gave us an example similar to this, but she didn't use a WHERE clause to check the right ID. Does it join the right table automatically?

When i run that sql statement i get this error:

Wrong syntax near'IF (NEW.retirado = 1) THEN UPDATE recolectores SET recolectore' en la linea 3

UPDATE

After reading this: Fire a trigger after the update of specific columns in MySQL

I now changed my statement, because i cant use UPDATE ON avisos.retirado, instead i have to use FOR EACH ROW and an IF statement to check for a specific column change. I also added a WHERE clause to actually hit the right id that i want to update, as suggested by @nbk

Here's the code now:

DELIMITER //
CREATE TRIGGER NumAvisosRecolectados_AU #after update
AFTER UPDATE ON avisos
FOR EACH ROW
BEGIN
    IF (NEW.retirado = 1) THEN
        BEGIN
            UPDATE recolectores
            SET recolectores.num_avisos_recolectados = recolectores.num_avisos_recolectados + 1
            WHERE reservas.FK_IDavisos = avisos.ID_avisos
            AND reservas.FK_IDrecolector = recolectores.ID_recolector
        END
    END IF;
END; //
DELIMITER ;

But i'm still getting a syntax error:

#1064 - Wrong syntax near 'END END IF;

Upvotes: 0

Views: 98

Answers (2)

Rasht Nightmare
Rasht Nightmare

Reputation: 1

Here the trigger is before updating a column in a directed table :

 DELIMITER $$
    
    CREATE TRIGGER update_trigger BEFORE UPDATE ON your_table_name 
    FOR EACH ROW
    
    BEGIN
    
    IF NEW.value <> 0 THEN 
    
    UPDATE table_name SET col_name = NEW.value WHERE col_id = NEW.id
    
    END IF;
    
    END ;

DELIMITER ;

Execute the query and everything will be fine, that's it!

Upvotes: 0

nbk
nbk

Reputation: 49385

You have need a `DELIMITER and the syntax is off

DELIMITER //
CREATE TRIGGER NumAvisosRecolectados_AU #after update
AFTER UPDATE ON avisos.retirado 
FOR EACH ROW
BEGIN
    IF (NEW.retirado = 1) THEN
        UPDATE recolectores
        SET recolectores.num_avisos_recolectados = recolectores.num_avisos_recolectados + 1
        WHERE idRecolector  = NEW.idAvisos;
    END IF;
END//
DELIMITER ;

New edit

DELIMITER //
CREATE TRIGGER NumAvisosRecolectados_AU #after update
AFTER UPDATE ON avisos
FOR EACH ROW
BEGIN
    IF (NEW.retirado = 1) THEN

            UPDATE recolectores
            SET recolectores.num_avisos_recolectados = recolectores.num_avisos_recolectados + 1
            WHERE reservas.FK_IDavisos = avisos.ID_avisos
            AND reservas.FK_IDrecolector = recolectores.ID_recolector;
    END IF;
END; //
DELIMITER ;

Upvotes: 1

Related Questions