[...] I need to know if:
* A row was inserted. * A row was deleted. * A row was modified.
I do this by creating three triggers on each table I want to monitor - one each of DELETE, INSERT, and UPDATE triggers. Each trigger writes information I need to know about the operation (such as the rowid effected) into a special table. After the change commits, I can go back and inspect the "change" table to see what happened.
The tricky part is knowing when any change has occurred to the database at all. If you have a single process that is doing all the changes, then this is simple. Just build a wrapper around your SQLite access routines that sends a signal after every operation. But if you have multiple processes updating the same database file, you'll have to use some kind of polling mechanism. Polling is not as bad as it might seem at first, though. On a typical workstation, you can do something like
SELECT * FROM ChangeLog;
about 10 times per second with no measurable CPU load. Even so, it is better if you have a single process doing the updates.
In one set of applications where I am using this technique, the application file format is an SQLite database and the triggers are used to implement Undo/Redo. When the user does a File->Open, it creates an in-memory SQLite database, ATTACHes the file the user specifies, copies in all the data, then closes the external file. Then I create the triggers in the in-memory database using just a few lines of Tcl script. The information stored by the triggers is the text of SQL statements needed to undo the change. There is an undocumented function named "quote" that is built into SQLite that makes the job of constructing such statements much easier.
In another application, I have to keep an XML representation of data in sync with an SQL representation of the same information. When SQL updates arrive, I apply them. Triggers are used to store rowids for rows that change in a special changelong table. Then when the commit occurs, the code can look at the changelog and make corresponding modifications to XML files.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
