Re: [sqlite] controlling changes
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 '-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
Re: [sqlite] controlling changes
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
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.
> 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.
> 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;
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
Re: [sqlite] controlling changes
Can you show me what the constraint would look like? Thanks, David On Sat, 2009-10-10 at 09:11 -0700, Harold Wood & Meyuni Gani wrote: > You can do it as a constraint. > > -Original Message- > From: David Bicking <dbic...@yahoo.com> > Sent: Saturday, October 10, 2009 9:08 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] controlling changes > > I have a table: > CREATE TABLE Assets > ( ControlDate Date > , Amt > ) > > 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. > > Can this be enforced via a trigger, or must I enforce that business rule > at the application layer? > > Thanks, > David > > > ___ > 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
Re: [sqlite] controlling changes
You can do it as a constraint. -Original Message- From: David Bicking <dbic...@yahoo.com> Sent: Saturday, October 10, 2009 9:08 AM To: sqlite-users@sqlite.org Subject: [sqlite] controlling changes I have a table: CREATE TABLE Assets ( ControlDate Date , Amt ) 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. Can this be enforced via a trigger, or must I enforce that business rule at the application layer? Thanks, David ___ 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] controlling changes
I have a table: CREATE TABLE Assets ( ControlDate Date , Amt ) 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. Can this be enforced via a trigger, or must I enforce that business rule at the application layer? Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
