Automatic Mysql updates

Question

Is it possible to have mysql automatically update a field in a table once a certain number is reached?

I have a table called badges and it has these fields

ID,name,image,level, percent

Can i make it so that if the percent field gets to a certain number (for example 50) , the level field is updated to 2 .

I think this would be a trigger ?

Answer 1

You can do several things.

In syntax

You can even write it in the syntax, allthough the value is not changed in the table then just something else is displayed. Like this

SELECT  ID, 
        name,
        image, 
        CASE WHEN percent > 50 THEN 2 ELSE level END AS Level, 
        percent FROM....

In Trigger

After each insert you have to count the value and then update accordingly. Maybe something like this

DELIMITER $$

DROP TRIGGER IF EXISTS databasename.badges_AUPD$$
USE databasename$$
CREATE TRIGGER `badges_AUPD` AFTER UPDATE ON `badges` FOR EACH ROW

// Added this line from Andreas Wederbrands answer which is the correct way

set new.level := case when percent < 50 then 0
                    when percent < 75 then 1
                    else 2;
    $$
DELIMITER ;

In Stored Procedures

You could simply schedule a event that executes the Stored Procedure. This however wont work if you really need the value to be changed once it reaches 50.

CREATE PROCEDURE `updateLevel` ()
BEGIN
UPDATE badges set level=2 WHERE precentage > 50;
END