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.
So maybe I should drop this down to the application:
'pseudo code
exec update items
if sqlite3_changes = 0
if creatable = 'N'
return error
else
exec insert into items
exec insert into changes
It just seemed that if I let sqlite handle this, I could just do one insert in
whatever host language makes sense, and not have to rewrite that pseudo code in
whatever language I chose to use for a particular task.
David
PS. Thanks Igor. Your solution was, of course, a thousand times better than the
best solution I had come up with.
--- On Tue, 8/10/10, Tim Romano <tim.romano...@gmail.com> wrote:
> From: Tim Romano <tim.romano...@gmail.com>
> Subject: Re: [sqlite] trigger or application code
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, August 10, 2010, 2:08 PM
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
