CODEWITHSUNDEEP
mysqltimetriggers
Alexandr Zhilinsky
Alexandr Zhilinsky

Reputation: 31

MySQL trigger to create timer

There is a special data type 'time' in MySQL.

How would a trigger look if I want my 'time' value to start counting when some state_id changes from 1 to 2? For example:

CREATE TRIGGER log_time AFTER UPDATE ON usr
FOR EACH ROW
BEGIN
IF usr.st_id = 2 THEN
#.... - thats what i dont know
END IF;
END;

It would stop counting when the state_id changes back from 2 to 1.

Upvotes: 1

Views: 1289

Answers (2)

Noob
Noob

Reputation: 31

Okay I'm new to coding and was wondering if this would even be possible and if so what would be the best way to get around this problem

Okay in my database I have a row for current time and a row for duration and I am wanting to find a way so that when the time is the value of T + D it would change a colour (green) ? Or even better if it could be done so if equal to or under 2 mins amber colour and over 2 mins red colour ( kind of like a traffic light idea)

Hope this makes sense 

 T     |   D
 ---------------
 22:50 |   6 (mins)

At 22:56 this would change colour on website

Thank You

Upvotes: 0

Techie
Techie

Reputation: 1491

Instead of starting/stopping the counter (I don't know if that's even possible), you should store the value in 2 different columns (and then substract to get the time when needed)

DELIMITER $$      
CREATE TRIGGER log_time AFTER UPDATE ON usr
    FOR EACH ROW
    BEGIN
        IF new.st_id = 2 THEN
            UPDATE <table> set <log_start_time> = CURTIME() <where_clause>;
        elseif new.st_id = 1 THEN
            UPDATE <table> set <log_end_time> = CURTIME() <where_clause>;
        END IF;
    END;
$$
DELIMITER;

OR in 1 column by storing initial value and then updating it in the trigger

DELIMITER $$      
CREATE TRIGGER log_time AFTER UPDATE ON usr
    FOR EACH ROW
    BEGIN
        IF new.st_id = 2 THEN
            UPDATE <table> set <logtime> = CURTIME() <where_clause>;
        else if new.st_id = 1 THEN
            UPDATE <table> set <logtime> = subtime(CURTIME(), select statement to get original value) <where_clause>;
        END IF;
    END;
$$
DELIMITER;

Upvotes: 1

Related Questions