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

Question

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;

P.Salmon · Accepted Answer · 2017-11-17 06:56:19Z

1

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 ;

answered Nov 17, 2017 at 6:56

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nahuel Ovejero Over a year ago

Thank you very much for your insight, really helpfull, I was clueless about MySQL and know a bit of PL-SQL. Thank you again

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related