CODEWITHSUNDEEP
mysqlsqltriggers
dapperwaterbuffalo
dapperwaterbuffalo

Reputation: 2748

question regarding use of MySQL triggers

I have seen MySQL triggers mentioned and I am curious whether they could be used for my scenario. That being:

lets say i have a table results_tb. Currently there is a field score and a field grade . So can I use triggers to update grade on the event of score changing?

so for example, lets say score stored was 40, the grade is updated to C, then again if the score is updated to 70, the grade is updated to A.

is this possible with triggers?

Upvotes: 0

Views: 134

Answers (2)

Brian Fisher
Brian Fisher

Reputation: 23989

You can use a trigger if you want, however, I agree with Nanne in terms of what appears to be better approach in the situation described.

DELIMITER |

DROP TRIGGER /*!50032 IF EXISTS */ `trigger_update_results_tb`|

CREATE TRIGGER `trigger_update_results_tb` BEFORE UPDATE ON `results_tb` 
    FOR EACH ROW BEGIN
       CASE FLOOR(NEW.score / 10)
        WHEN 0 THEN SET NEW.grade = 'F';
        WHEN 1 THEN SET NEW.grade = 'F';
        WHEN 2 THEN SET NEW.grade = 'F';
        WHEN 3 THEN SET NEW.grade = 'F';
        WHEN 4 THEN SET NEW.grade = 'D';
        WHEN 5 THEN SET NEW.grade = 'C';
        WHEN 6 THEN SET NEW.grade = 'B';
        ELSE SET NEW.grade = 'A';
       END CASE;
    END;
|

DELIMITER ;

Upvotes: 1

Nanne
Nanne

Reputation: 64399

Well, you could create a trigger that fires on 'update'. The manual is quite clear. But you would have to make some sort of "list" of grades that correspond with your scores. So you'd have something of a list that compares scores to grades. That sounds suspiciously like a "lookup table" to me.

Couldn't you just add a table for grades that belong to a certain score to your database, and if you query your current results_tb, add a join to that table?

So instead of

SELECT score, grade FROM results_tb WHERE id=1

you'd get

SELECT r.score, l.grade FROM results_tb r 
JOIN lookup l ON r.score = l.score
WHERE r.id=1

Upvotes: 1

Related Questions