How do I record an application session duration in SQLite?

Question

Objective: Log the session duration of the user on the application.

I have a session_log table:

CREATE TABLE IF NOT EXISTS "session_log"
  (
     "id"         INTEGER PRIMARY KEY,
     "login"      DATETIME NOT NULL,
     "logout"     DATETIME,
     "duration"   REAL,
  );

and I am trying to create a trigger:

CREATE TRIGGER IF NOT EXISTS calc_session_dur
AFTER UPDATE ON session_log
BEGIN
    UPDATE session_log
    SET    session_log.duration = IIF(NEW.logout <> OLD.logout, JULIANDAY(NEW.logout) - JULIANDAY(OLD.login), 0)
    WHERE  OLD.id IN (SELECT id FROM "session_log" ORDER BY column DESC LIMIT 1)
END;

I keep getting errors when trying to create the trigger, but the error reporting is quite unclear:

Execution finished with errors.
Result: near ".": syntax error
At line 133:
CREATE TRIGGER IF NOT EXISTS calc_session_dur
AFTER UPDATE ON session_log
    WHEN OLD.logout <> NEW.logout
BEGIN
    UPDATE session_log
    SET    session_log.

How do I achieve my objective? What is the mistake in the code?

Answer 1

CREATE TRIGGER IF NOT EXISTS log_logout
AFTER UPDATE OF logout 
ON session_log
BEGIN
    UPDATE session_log
    SET    duration = (JULIANDAY(logout) - JULIANDAY(login)) * 24 * 60 --minutes
    WHERE  id  IN (SELECT id FROM session_log ORDER BY id DESC LIMIT 1);
END;