On 10 Aug 2010, at 7:45pm, David Bicking wrote: > Actually, the amount in items is a running balance of the changes. Mind you, > there are other fields in items (and in changes) that I did not show as they > didn't seem to impact this decision. > > In that 8% case where an item is to be created, the "change" is in fact the > creation of the item. > > Originally, I would check to see if the item existed, and create it if > needed. But given it is such an infrequent event, it seemed better to try to > update and only create if the update failed. Though looking at Igor's > suggested trigger, it is just doing the whole select where not exists thing > anyway, actually twice, once to test if the ABORT should be raised, and > second time to see if the record should be inserted.
Two more possibilities: A) Change your trigger. Have the trigger check to see whether the 'item' exists, if not, create a new row in the "items" table, if it does, update the existing row. B) Abandon the "items" table altogether. If it's only purpose is to provide you with the running total, just calculate the running total whenever you need it using something like SELECT sum(AdjAmount) FROM changes WHERE Code = 'nnn' If you have an index on the Code column this may be pretty fast. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
