On Sat, 2009-10-10 at 19:31 +0100, Simon Slavin wrote:
> On 10 Oct 2009, at 5:08pm, David Bicking wrote:
>
> > I have a table:
> > CREATE TABLE Assets
> > ( ControlDate Date
> > , Amt
> > )
>
> There is no such column type as 'Date' in SQLite. You got TEXT,
> INTEGER, REAL. Make sure you know what's going into that field. Also
Yes, I know there is no Date type in Sqlite. I would likely be storing
the date as text in a 'yyyy-mm-dd' format.
> I don't understand your primary key for the Asset TABLE. Is
> 'ControlDate' a primary key ? In other words can you have no more
> than one record for any one day ? Your answer to this changes how the
> TRIGGER has to work.
>
I haven't entirely decided what the primary key would be. Possibly an
artificial autoincrement value, but probably a composit key of a half
dozen fields not shown as I didn't think them important to the problem.
The control date likely would be part of the composite key. But
certainly you can have more than one record with a given ControlDate.
> > Now, the business rules are you can INCREASE the Amt if the Current
> > Date
> > is the ControlDate in the record. You can DECREASE the amount if the
> > Current Date is greater than or equal to the ControlDate.
>
> You mean 'greater than or less than', right ? Otherwise you
> contradict yourself.
>
Um... I do believe I have gotten it wrong. There are in fact two
controldates, on controldate #1, you can increase the value. ON or after
controldate #2, which will always be after controldate #1, you can
decrease the amount. I guess controldate#1 would be better called the
creation date, and controldate #2 would be maturity date. They are a
function of each other, but I am not exactly sure about that yet. More
research to do.
> > Can this be enforced via a trigger, or must I enforce that business
> > rule
> > at the application layer?
>
> You can absolutely use a TRIGGER for this, but you need some way of
> making the current date (you mean 'right now' ?) available to SQLite.
> Something like
>
> CREATE TRIGGER Assets_change_amt
> BEFORE UPDATE ON Assets
> FOR EACH ROW BEGIN
> SELECT RAISE(ROLLBACK, 'Amount can only increase on a
> particular day.')
> WHERE (new.amt <= old.amt) AND (new.ControlDate = currentDate);
> END;
>
I'll test if I can do date functions in triggers. But this tells me what
I need to know. Sort of a D'oh! moment as "new.amt <= old.amt" NEVER
occurred to me. Thanks for pointing out what should have been plainly
obvious even to me!
> But I just wrote 'currentDate' in there and you won't be able to use
> that. What I don't know is how you have formatted the contents of the
> ControlDate field. If you have been consistent with SQLite, then you
> can use something like
>
> date('now')
> strftime('%s','now')
>
> That is assuming you can use date functions inside TRIGGERs. I think
> that depends on which version of SQLite you're using. For that you
> need someone more expert than I am.
>
> Simon.
> _______________________________________________
> 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
