David, Your approach contravenes "best practice" by violating the core referential integrity paradigm: your CHANGES table refers to an item not yet in the ITEMS table and actually governs whether an ITEM item can be created. The child is giving birth to the parent. This is unnecessarily convoluted.
In your example, you have the ITEMS table track the most recent amount. That's all it's doing. Now, if that's all you want this table to do ( you don't want to have a full ITEMS master table with item-description, UPC codes, etc etc, for example), you can eliminate the ITEMS table. You could always get the most recent amount with a simple query. select amount from changes where code = ? and changedate = ( select max(changedate) from changes where code = ? ) or in the alternative select amount from changes where code = ? order by changedate desc limit 1 The problem with this approach is that any [code] value under the sun is acceptable; there's no ITEMS table to prevent invalid codes via a foreign key declaration. Regards Tim Romano On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > David Bicking <dbic...@yahoo.com> wrote: > > I am building an application with these two tables: > > > > CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable); > > CREATE TABLE items(Code, Amount) > > > > Now, what I would like to happen, I insert in to changes, and it updates > the Amount in items. > > > > I can get that with > > > > CREATE TRIGGER changes_after_insert on changes > > BEGIN > > Update items set Amount = Amount + new.AdjAmount where code = new.code; > > END; > > > > And for 90% of the time, that will do what I want. > > But for 8% of the time, the items entry won't be there, so I would like > to insert a new items entry but only if the Creatable > > flag is not 'N'. > > The remaining 2% of the time, the items entry doesn't exist and the > Createable flag is 'N', and I need to ABORT the insert and > > report the error back to the application. > > > > My question is can all this be done in a trigger? > > Yes, but it's pretty awkward: > > BEGIN > select raise(ABORT, 'Item does not exist') > where new.Creatable = 'N' and new.Code not in (select Code from items); > > insert into items(Code, Amount) > select new.Code, 0 > where new.Code not in (select Code from items); > > update items set Amount = Amount + new.AdjAmount > where code = new.code; > END; > > > Or is this type of logic better handled at the application level? > > Quite possibly. > -- > Igor Tandetnik > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
