Database-Design

如何在 SQLite 中記錄應用程序會話持續時間?

  • December 11, 2021

目標:記錄使用者在應用程序上的會話持續時間。

我有一個 session_log 表:

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

我正在嘗試創建一個觸發器:

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;

嘗試創建觸發器時,我不斷收到錯誤,但錯誤報告很不清楚:

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.

我如何實現我的目標?程式碼中的錯誤是什麼?

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;

引用自:https://dba.stackexchange.com/questions/303807