Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1037

Re: Enforcing an Order to Validation

Cross-posted to 2 groups.

Show all headers | View raw

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.

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.)

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.

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.

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. 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-10 12:42 -0700
  Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-10 23:47 +0200
    Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-10 15:05 -0700
      Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-11 09:01 +0200
        Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-11 09:50 -0700
          Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-11 22:29 +0200
            Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-11 22:10 -0700

csiph-web