CODEWITHSUNDEEP
mysqltriggers
Nahuel Ovejero
Nahuel Ovejero

Reputation: 87

MySQL Triggers that updates a table based on inserted and stored values

I have to create a trigger in a table proyect, to check if all the tests for the proyect are 'ready'. If all of them are ready, I need to search the proyect and put the status of the proyect as 'ready'. If not, Incomplete.

I'm aware that I need to create one trigger for the UPDATE, and another one for the INSERT. I've planned something like this, But I can not make it work.

I created this solution, if the total number of test for that proyect, is below to the number of test ready for that proyect, then the proyect is not ready. and if both are equals, the proyect is ready.

I don't know why it doesn't work:

CREATE TRIGGER update-proyect
AFTER INSERT ON tests 
FOR EACH ROW 

SET @total = select COUNT(*)
              from tests
            where IdProyect= NEW.IdProyect;


SET @ready = select COUNT(*)
            from prueba
            from tests
            where IdProyect= NEW.IdProyect 
            AND status = 'ready';

IF (@total == @ready) 
   UPDATE proyect SET status = 'Ready' WHERE IdProyect = NEW.IdProyect;
ELSE
   UPDATE proyect SET status = 'Incomplete' WHERE IdProyect = NEW.IdProyect;
END IF;

Upvotes: 0

Views: 33

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Loads of errors update-proyect is an invalid trigger name change to update_proyect, If you have more than 1 statement in a trigger they have to be encased in a begin and end statements, a select after a set has to be encased in braces, there is no == comparison relation operator in mysql either use = or null safe equals <=>, an if statement has to have a then statement. This at least syntaxes.

DROP TRIGGER IF EXISTS update_proyect;
DELIMITER $$

CREATE TRIGGER update_proyect
AFTER INSERT ON tests 
FOR EACH ROW 
BEGIN
SET @total = (select COUNT(*)
              from tests
            where IdProyect= NEW.IdProyect
                );

SET @ready = (select COUNT(*)
            from prueba
            #from tests
            where IdProyect= NEW.IdProyect 
            AND status = 'ready'
            );

IF (@total = @ready) THEN
   UPDATE proyect SET status = 'Ready' WHERE IdProyect = NEW.IdProyect;
ELSE
   UPDATE proyect SET status = 'Incomplete' WHERE IdProyect = NEW.IdProyect;
END IF;

END $$

DELIMITER ;

Upvotes: 1

Related Questions