MySQL Trigger on Table A, INSERT OR UPDATE table B with TIMESTAMPDIFF()

Question

I have 2 tables - clients and client_history.

CLIENTS:
    cID | last_seen  (LAST_SEEN is updated every 2 mins by my application)

CLIENT_HISTORY :
    ID | cID | left_left | time_arrived

I'm trying to:

Record the comings and goings of clients over time. So when clients.last_seen is older than 5 minutes, INSERT record into client_history with the cID, and the clients.last_seen values.

I want something like:

 BEGIN
  IF (-- something here is true)
  THEN
    INSERT INTO client_history
    SET
      cID=old.cID,
      last_seen=old.last_seen;
  ELSEIF (-- something here is true)
  THEN
    INSERT INTO client_history
    SET
      time_arrived=old.last_seen
  END IF;
END

I've been looking at using

IF (TIMESTAMPDIFF(MINUTE,c.last_seen,NOW()) > 5) for the first IF condition.

I'm new to triggers, and I don't know if my logic is good, or if there's a simpler or better way to do this. I was wondering if I could introduce a flag column to indicate a client left or something like that.

Answer 1

This might get it for you.

DELIMETER $$
CREATE TRIGGER monitor
AFTER UPDATE
ON clients
FOR EACH ROW
BEGIN
IF (TIMESTAMPDIFF(MINUTE, OLD.last_seen, NEW.last_seen ) > 5)
THEN INSERT INTO client_history (cID, left_left)-- or whichever column here
SELECT NEW.cID, NEW.last_seen FROM client_history;
END IF;
END $$
DELIMETER ;

I thought to use the NEW.last_seen value in the logic instead of NOW() function.