0

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?

1
  • 2
    You shouldn't store the value that can be calculated at runtime in the first place. To your error: update takes unqualified column names in the set clause.
    – mustaccio
    Dec 10, 2021 at 23:00

1 Answer 1

0
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.