From: esquel@sommarskog.se   
      
   Gene Wirchenko (genew@ocis.net) writes:   
   >      Because if I did that every time I ran into an issue, I still   
   > would not know what is the better *general* solution.  I want to know   
   > that.  If a particular application is written less efficiently, that   
   > is acceptable to me as long as I have a generally good method.  (I can   
   > fine-tune later.)   
      
   There may not be any best "general" solution. And if there is, it may not   
   be the best for you.   
      
   My main language besides SQL is Perl, and in Perl the motto is "There is   
   more than one way do it!". I have found that this applies very much to   
   SQL as well.   
      
   As for the question to use stored procedures or triggers, this is not always   
   an easy question. At the same time, triggers and procedures serves different   
   purposes.   
      
   In the bottom of it all, there is a table. A table describes and entity   
   and has a number of attribuets. To the table comes a number of rules. Some   
   are very simple, like the customer id of an order cannot be NULL. Others   
   are far more complex. Some rules can be expressed with constraints,   
   and this is generally the preferred way.   
      
   But not all rules are easily expressed with a constraint. For instance,   
   you can require that the customer of an order must exist with help of a   
   foreign-key constraint. But the requirement that the customer of a new   
   order must be an active customer, is not equally easily expressed with a   
   constraint. To this end we with have triggers. Triggers are general   
   features, and you can do about anything in a trigger. But there are also   
   several disadvantages compared to constraints:   
   1) They are more complex to write and not the least they require more   
      routine code to raise errors etc.   
   2) A trigger can more easily disappear than a constraint.   
      
   You could say that a trigger is further away from the table than a   
   constraint. Nevertheless, the most common view is that the trigger   
   is part of the table and holds up the rules for the table.   
      
   A stored procedure on the other hand is an autonomous module and is   
   not tied to a table. Simply put - it is application code in the database.   
   But if you think of, this is also true for a trigger. The trigger is   
   tied to a table, but if it operates on other tables, it's an application   
   with regards to those tables.   
      
   Many advocate putting complex cascading updates in stored procedures   
   rather than in triggers. And there is certainly a merit in this. If   
   you identify a rule which says that if a number of items are added to   
   and order, the inventory should be reduced, it makes sense to have this   
   logic in one place - in a stored procedure. Not the least if you later   
   in the procedure need to operate to the reduced inventory. If you rely   
   on a trigger to do the work you have to split your logic in two disjunct   
   places.   
      
   There is also the problem that if trigger is disabled to dropped, you   
   will not notice, which means that the integrity of the database is   
   silently violated. Very bad. If someone drops a stored procedure,   
   this results in an error, and the database is protected.   
      
   But the drawback with stored procedure is that if someone bypasses the   
   stored procedure and updates the table directly for maintenance reasons   
   or whatever, there is nothing to enforce the rules. The person doing   
   the update must be aware of that he manually has to reduce the inventory.   
      
   By means, this is not a simple question. Over the years, I have come to   
   more and more favour triggers over stored procedure for actions. Not   
   the least because in the environment I work, there are updates directly   
   against the tables by people who don't know as much as they should.   
   (Not end users, but support people, often when the application puts up   
   a roadblock for the suers.)   
      
   But doing everthing in triggers also means that code is more difficult   
   to maintain and understand. Stored procedures may also be nested, but   
   if one procedure calls another you see that in the code. But if a table   
   has a trigger that makes a lot of changes, this is nothing which stands   
   out in the same way.   
      
   So this is largely a non-answer to your question. But it is my hope that   
   you will be confused on a higher level.   
      
      
   --   
   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)