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
