XPost: microsoft.public.sqlserver.programming
From: genew@ocis.net
On Thu, 10 May 2012 23:47:32 +0200, Erland Sommarskog
wrote:
>Gene Wirchenko (genew@ocis.net) writes:
>> Suppose I have a stored procedure to handle insertions and
>> updates to a database table. While my front-end will have checked the
>> data somewhat but non-authoritatively, I want the database to check
>> it, too, as the authoritative validator. I see three types of
>> validation: primary key, foreign key, and non-key data. These are in
>> decreasing order of importance.
>>
>> Is there any way to force the order of the checking? For
>> example, on an insertion, if the PK already exists, that error is more
>> important than that a Postal Code is in the wrong format.
>
>You have a couple of devices to your disposal:
>
>o Stored procedures.
>o INSTEAD OF triggers.
>o Constraints, including unique indexes.
>o AFTER triggers.
>
>Checking is roughly performed in this order. That is, you typically
>first check in your stored procedure before you try to do the operation, and
>then then operation consists of these steps. But in theory you could
>perform post-statements checks in your procedure.
>
>In practice, order is not that important. At least not in most cases.
>After all, the basic assumption is that errors should not occur, why
>errors are exceptional things.
One of the issues that I have with database errors is the
disconnect between them and the frontend. When I do error checking on
the front-end, I am careful check in a logical order. Normally, this
means that an earlier field is checked before a later one. It would
be silly to check in a random order.
I want the database errors to come out in a similar order so that
they make sense in terms of the front-end. If I have this, I can also
set the focus to the erroneous corresponding field.
>Of course, that depends on the type of operation. For instance the
>first time we have written a stored procedure, errors are very much
>expected (at least when I write them). It would be very tedious if
>SQL Server would give up on the first error encountered, but we do
>get a bunch of errors. (Then again, you don't get the full list of
>errors either.)
It might be exactly what is wanted.
>If you are getting data from an external source where you expect that
>1-2% of the data will be incorrect, you have a case. But having a written
>a lot of error-validation code, I have in many cases let it suffice
>with stopping at the first error.
It will be from user input thus error-prone.
>Now, as long as you write the code yourself, you have control and
>can decide in which order to make the checks. Ehum, with one qualification:
>if you have multiple triggers, you can only controll which is the first
>and the last trigger.
>
>On the other hand, there is no way to control in which order constraints
>are fired. Nevertheless, you should use constraints as far as possible,
>because they are so much easier to code than any other checks.
>
>And in practice, you have no reason to be worried. I have not tested
>carefully, but I'm fairly confident that all index uniqueness is
>verified before FK relations. (You can easily see this in the query
>plan). Where the CHECK constraints are checked, I'm less sure of.
>But you could easily test.
I am trying to find out what is the guaranteed behaviour. That
is safer.
>Lastly, the main purpose of constraints and triggers is protect
>the database against the application. The application should never
>permit a user to enter a duplicate or an FK violation.
I think that statement should be that the database should never
permit such, but the application should be free to do no error
checking. The application also doing error checking makes for a more
friendly app, but I would prefer correctness FIRST.
Sincerely,
Gene Wirchenko
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
