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
