Alexandr Zhilinsky Alexandr Zhilinsky - 1 month ago 7
MySQL Question

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.

Answer

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;