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? Or is this type of logic better handled at the application level? I kind of want to have it in a trigger because I would like to have different client applications update the database and I don't want to have the Create items entry logic sitting in the code of each application. (Especially since one possible application could be a simple shell script that uses the command line tool to insert the values.) What would be a "best practice" in this type of situation? Thanks, David _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
