Mysql automatic updates
Is it possible that mysql will automatically update a field in the table after reaching a certain number?
I have a table named badges
and it has these fields
ID,name,image,level, percent
Can I make it so that if the percentage field falls on a certain number (like 50), the level field is updated to 2.
I think this will be the trigger?
source to share
There are several things you can do.
In syntax
You can even write it down in the syntax, although the value is not changed in the table and then only 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 need 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 can simply schedule an event that executes the stored procedure. This, however, doesn't work if you really want the value to change when it hits 50.
CREATE PROCEDURE `updateLevel` ()
BEGIN
UPDATE badges set level=2 WHERE precentage > 50;
END
source to share
Derived values are best computed on read instead of store, so you can change your thresholds whenever you want
select ID,
name,
image,
case when percent < 50 then 0
when percent < 75 then 1
else 2
as level,
percent
from some_table;
Another option is to set the level value in the trigger
create trigger set_level
before insert on some_table
for each row begin
set new.level := case when percent < 50 then 0
when percent < 75 then 1
else 2;
end
Do the same for before update
.
You don't have to recalculate all table levels when inserting / updating just one or more rows.
source to share