keeping a log table in sqlite database?

Question

I'm looking for a way to set up (via SQL) a log table containing everything that had been done to my sqlite database (preferably in terms of the insert, create table etc. statements as issued to the database). I'm sure there are way to do it via setting trigger on each table, but that is just WAY too much work and does not bode well if I change the database's schema later. Is there a catch-all global thing that work on the database (like trigger on database itself)?

I'm also open for other suggestions for keeping records of changes done to sqlite database so that I can look back months later on the changes.

(Programatically of course there are ways but I can't be sure that my program is the only program writing to the database).

Answer 1

Though the following doesn't meet all your requirements, you may wish to see one way to do it. Mike Chirico's SQLite Tutorial has a section on Logging All Inserts, Updates, and Deletes that mimics the functionality of MySQL's binlog.

It relies on triggers that must match the schema for each table whose changes you wish to track. That is, if your table has a field called "a", then the logging table needs to keep track of "aOLD" and "aNEW". In this way, the trigger is able to record updates, inserts, and deletes made to the fields in that particular table.