Using triggers vs. Multiple Functions in Stored Proc 
Author Message
 Using triggers vs. Multiple Functions in Stored Proc

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.

Thanks,

Jason

*** Sent via Developersdex http://www.***.com/ ***
Don't just participate in USENET...get rewarded for it!



Mon, 23 Aug 2004 06:03:50 GMT
 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



Mon, 23 Aug 2004 07:28:52 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. function vs. stored proc

2. Using a stored proc to call another stored proc

3. SP vs View vs Function vs Trigger

4. Using multiple Exec statements in stored proc

5. calling an oracle stored proc (function) using DAO

6. using stored proc results in other proc or select

7. Stored Procedures -vs- Stored Functions

8. Stored Proc Calling Another Stored Proc

9. How to get results from Stored Proc within a Stored Proc

10. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

11. calling stored proc from stored proc via variable


 
Powered by phpBB® Forum Software