On 1 Oct 2010, at 4:44pm, Brian Curley wrote:
> My business case would be in the realm of logging, for example. If I'm
> using an audit-centric trigger, I'd probably want to store field and value,
Don't forget that one 'UPDATE' command can result in many different fields
being changed. So that should be something like 'fields and values'.
> as opposed to the entire row as only one or two fields might change and to
> conserve space. I could probably rig a rube goldberg series of triggers to
> store and compare old.* and new.* values to reinsert elsewhere but if the
> column names are already present it'd be a nice little shortcut. My primary
> use would be via the shell...
My reading of that suggests you want to use triggers to create a log of all
changes made to a table (or possibly database). This will result in a very
slow system since any change which would normally result in a single change to
the database would result in many calculations and rows written. You would
also have to take your schema apart and make it apparent inside your SQLite
code ...
if the field 'address' changed (i.e. old.address != new.address), note this in
the log,
if the field 'phoneNumber' changed, note that in the log, ...
which tends to be a recipe for disaster when you want to change your schema
later.
Rather than use triggers for this, you might come up with your own routine to
call to make changes. For example, where you'd normally pass an 'UPDATE'
command directly to SQLite, you pass it to your own routine, which first logs
the command sent, then passes it to SQLite. So rather than log the result of
each command executed, you simply log the command itself. This requires no
analysis, and only one extra write per write, so it results in a faster system,
and doesn't require you to change your triggers when you change your schema.
Another way to do it would be to use sqlite3_update_hook() or sqlite3_trace(),
and have them write your own log file for you. I have no real idea how this
would work but perhaps someone else does.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
