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
