Database-Design
如何在 SQLite 中記錄應用程序會話持續時間?
目標:記錄使用者在應用程序上的會話持續時間。
我有一個 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;