1

I have a table that looks like this

CREATE TABLE IF NOT EXISTS "ebooks"
  (
     "id"         INTEGER PRIMARY KEY,
     "name"       TEXT NOT NULL,
     "author"     TEXT DEFAULT 'N/A',
     "genre"      TEXT DEFAULT 'N/A',
     "path"       TEXT NOT NULL,
     "ext"        TEXT NOT NULL,
     "pages"      INTEGER DEFAULT 0,
     "size"       INTEGER NOT NULL,
     "folder"     TEXT NOT NULL,
     "summary"    TEXT NOT NULL DEFAULT 'N/A',
     "timestamp"  DATETIME NOT NULL DEFAULT (Datetime(CURRENT_TIMESTAMP, 'localtime')),
     UNIQUE("path"),
     UNIQUE("name")
  );

What I want to do is create a table that will log any changes that happen in the above table including: deleted records, and updated fields. I am not sure how many tables and triggers I need.

I think the logging table should look like this:

CREATE TABLE IF NOT EXISTS "ebooks_log"
  (
     "id"         INTEGER PRIMARY KEY,
     "book_id"    INTEGER NOT NULL,
     "name"       TEXT NOT NULL,
     "action"     TEXT NOT NULL,
     "field"      TEXT NOT NULL,
     "timestamp"  DATETIME NOT NULL DEFAULT (Datetime(CURRENT_TIMESTAMP, 'localtime'))
  );

Where action would say 'update' or 'delete' and field would be for example 'author, genre' meaning that in that update query, both the authors and genres fields were updated.

How do I this or how do I achieve "logging information" in a better way? Thank you.

1
  • Coincidentally, I'm looking for something very similar. Adding my two cents here, if someone could show if there is something similar to T-SQL's EVENTDATA() as mentioned here, things could become much, much easier. Dec 10, 2021 at 21:50

0

Your Answer

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

Browse other questions tagged or ask your own question.