Re: "Upcalls" (sort of) from the database

Don Y wrote:
> Hi,
>
> I wasn't prepared to ask this question, yet :< but
> all the talk of stored procedures, etc. suggests
> this might be a good time to venture forth...
>
> Humor me: assume I have done the analysis and *know*
> this to be correct for my situation :>
>
> I want to embed a good deal of the invariant aspects
> of my application *domain* in the databases that
> service it -- instead of in the applications riding
> *above* them.
Keeping in mind that you've done plenty of analysis, I'd raise the
standard cautionary note that you have to be pretty certain that you're
right about what's invariant if you're going to couple your application
logic very tightly to your data model.

> Anyway, the problem I have is how to handle cases
> where the "database" needs user confirmation of an
> action (!). I.e. it has verified that the data
> coming in is legitimate (adheres to all of the rules)
> and *can* be stored in the appropriate tables -- BUT,
> notices "something" that causes it to wonder if the
> user REALLY wants to INSERT this data. The database
> would like to alert the user to what it has noticed
> and get confirmation from the user (of course, I
> mean my *application* wants to do this -- but, based
> on observations made *by* the database, itself).
I've been dealing this myself, as it happens, in the context of
importing and validating data (an ETL kind of thing). My answer is to
use temporary tables as a way of staging the data. One of the
advantages of that is that you can wrap up the DDL for the temporary
table inside your code (whether in or out of the database) and thus
insulate that part of the process from other changes in the destination
tables. Then you can fire off whatever rules you like about whether to
alert the user of certain conditions with the data without worrying
about commits. Of course the transaction approach can probably handle
that, too.

Cheers,

Eric