views:

20

answers:

1

I have a table with a trigger (for update) to set the LastEditedTime field when a change if made - simple enough. I also have a trigger (after update) to set LastStatusChangedTime. The problem is as follows:

Is there any performance issue if i use two triggers for update? or can i combine two triggers into one?

I'm wondering how others deal with this issue.

Thanks.

A: 

I do not think you can combine them simply because the FOR UPDATE works before the TSQL is executed whereas the AFTER UPDATE is done after the execution is complete.

The performance is entirely dependent on the quality of your code.

There is no performance difference if the same code is in one trigger or two triggers.

Over the past three years, I have yet to encounter a single thing that I could not do without triggers. I personally stay away from triggers as much as possible. They are just as easy to tweak as any other SQL Statement, but they are the hidden factors behind any edit operation on data, and as such, I am wary of them.

If you want your trigger to run for a mass update, you have to code it to run that way, otherwise you will just run it for the last row. For arguments sake, let's say a trigger takes 1 second to run. If you are doing a RBAR, and run a update worth 100 rows, your SQL Query will not return until 1 Min 40 Seconds.

Curiously enough, most developers that I have found that use Triggers (barring INSTEAD-OF) are also fond of cursors.

All in all, my 2-pence is to stay away from them as much as possible.

Raj More
That last statement is not true. A trigger in SQL Server runs once for the entire set of data being updated. It doesn't run once for each row. Assuming that you've coded it correctly and in a set-based manner you shouldn't see anywhere close to a linear growth. I agree with your other reasons for being wary of triggers though.
Tom H.
@Tom H answer edited for comments
Raj More