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
