Using triggers vs. Multiple Functions in Stored Proc
Quote:
> When I insert a row into one of my tables, I need to update preexisting
> rows based on some condition.
> Should this be handled in an INSERT trigger rather than having both an
> INSERT clause and an UPDATE clause in my Insert procedure? I'm guessing
> that it should so these rules will be enforced even when the DBA is
> adding rows directly into the table.
> If I'm going to UPDATE something in an INSERT trigger, at what point
> during the operation is this performed...before or after the actual
> insert? Either being the case, the UPDATE needs to happen as part of a
> transaction with the INSERT. Thus, if any fails, they both fail.
> Can this operation be handled using the trigger? If so, could you
> advise? I'm not a DBA, so I'm somewhat unclear about this.
Yes, you could use a trigger for this, and the UPDATE would be part of
the transaction. Whether the UPDATE will happen before or after the
INSERT, you can actually choose if you are on SQL2000 where you have
both INSTEAD OF triggers and AFTER triggers. The normal way would be
to use an AFTER trigger though.
Whether to use a trigger or put it in the INSERT procedure... This is not
a question with an apparent answer. The good thing with a trigger is
that it will cover ad hoc-updates too. The bad thing is that a trigger
can be disabled or dropped, and the application will job along just
merrily never noticing that the trigger is missing.
In our shop we're quite restrictive with updates in triggers and prefer
to handle this in our stored procedures. But then we have an architecture
where most tables are only updated from one single procedure.
--
Erland Sommarskog, SQL Server MVP
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
