Chuck,
One way would be to twist the DB to do the complete restraint to me, something like to a pseudo-code “inserted_row.attr>=MAX(SELECT attr FROM this_table WHERE another_attr.isValid) AND couple more similar conditions” -- frankly I am not sure whether the
database (FrontBase) can do that at all, and if it can, definitely I don't know how to.
I think you can do it as a Check constraint. It needs to be a boolean _expression_, but I am not sure it can include selects. A quick experiment would show if it can.
It looks like FrontBase allows me to use SELECT in check constraints, but I've bumped into another problem -- I need also to join to access values from another table, and I don't know how to express „column from the row just being inserted, not from any
other table in the SELECT“?
My constraint _expression_ (considerably simplified for readability) looks like this:
(select max(po.C_PRICE) from T_PRICE_OFFER po, T_AUCTION auc where po.C_AUCTION_ID=auc.C_UID and po.C_PRICE<=auc.C_MAX_PRICE and po.C_CREATION_DATE<C_CREATION_DATE)<C_PRICE
to express a condition “PRICE of the inserted PRICE_OFFER must be higher than all PRICEs of all older PRICE_OFFERs belonging to the same auction, whose PRICE does not exceed the auction's MAX_PRICE”.
The problem is with the “older PRICE_OFFERs” part (“WHERE ... po.C_CREATION_DATE<C_CREATION_DATE”) -- FrontBase complains that C_CREATION_DATE is ambiguous. Well it is -- all the tables in question contain such a column -- but how should I prefix it to
tell the server „this is the CREATION_DATE of the row which is being inserted“?
Thanks a lot,
OC