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

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

Re: SSE 2008: Handling Transaction Batches

Show all headers | View raw

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

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

Thread

SSE 2008: Handling Transaction Batches Gene Wirchenko <genew@ocis.net> - 2011-05-05 16:25 -0700
  Re: SSE 2008: Handling Transaction Batches Erland Sommarskog <esquel@sommarskog.se> - 2011-05-06 23:46 +0200
    Re: SSE 2008: Handling Transaction Batches Gene Wirchenko <genew@ocis.net> - 2011-05-06 15:06 -0700
      Re: SSE 2008: Handling Transaction Batches Erland Sommarskog <esquel@sommarskog.se> - 2011-05-07 15:09 +0200
        Re: SSE 2008: Handling Transaction Batches Henk van den Berg <hvandenberg@xs4all.nl> - 2011-05-07 18:31 +0200

csiph-web