XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)