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.  So, I let the database, itself, do
> sanity checking of data on input -- if the database
> rejects the INSERT, the application *knows* there is
> something wrong with the data (instead of building
> libraries to check each datum in each application
> and *hoping* that the checks are implemented
> consistently from one application to the next, etc.)
>
> 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).
>
> By way of example, the *toy* application I am playing with
> to explore my implementation options is a "book" database;
> it tracks titles, books, authors, publishers, etc.
> The sort of thing a library could use to manage its
> collection.
>
> Assume the user tries to INSERT an entry for a "book".
> Legitimately, this can be:
> - a new title that the database has never seen before
> - a new title by an author with other titles in the database
> - an existing title thus another copy of that title
>
> However, it can also just *appear* to be a legitimate new
> title!
>
> For example, the title may match an existing entry -- but
> the author may be different (e.g., misspelled, or some
> "other" author listed on a book having multiple authors, etc.).
> Ideally, I would like the database to suspend the INSERT,
> ask for confirmation (and "why") and then, either commit
> the INSERT or abort it (based on the user's response).
>
> Nearest I can imagine, there's only one ways I can do this:
> issue a query that looks for these types of problems and
> based on the result, let the *application* prompt the
> user for confirmation.  Then, *if* confirmed, do the real
> INSERT.

You could *insert* the data and then *rollback* the transaction. Then
you would *know* the data is *valid*.
Only if the user *confirms* the action, then you do it *again* and
actually *commit* the transaction.

P.S. these* *stars* are *unnerving* ;-)

bkw