June 6, 2003 at 7:49 am
Greetings, I am curious if anyone has thoughts about an update trigger that when fired, updates the very same record that caused the trigger to fire. This was not allowed in SQL7.
I'm running SQL 2000 and the application requires that a row's status is set whenever data in the row changes. The development team elected to do this with a trigger - when a row is updated, the trigger examines the data to determine what status it should be in and then set's that status.
The trigger has been in production for 6 months and it does work, but we have an abnormal amount of deadlocks and most traces point back to Stored Procedures that update or delete rows from this table.
Any thoughts about update triggers that update the same record or alternate methods to accomplish the same effect?
Thanks for you feedback!
June 6, 2003 at 8:27 am
It would be interesting to review the code of your trigger. You are probably already doing this but the code needs to be very specific in determining to make an additional change in order to avoid extra looping. The first condition before other processing ought to be the status since that is the part that is changing.
It appears that you have a fairly high volume (deadlocks don't happen much in low volume worlds). What is the consequence of creating a "queue" entry somewhere and updating sets of rows to the new status rather than doing it on each update?
Just some thoughts - they probably echo what you've already considered.
Guarddata-
June 6, 2003 at 8:27 am
do you have a check in the update trigger to exit if status is updated - like this
IF UPDATE(status)
RETURN
that should stop any recursion
we have a similar situation - the field in question is placed in another table - with a datetime field - so we know what the latest status is -along with a history of changes
June 9, 2003 at 8:14 am
Until recently I haven't had to deal with databases set with cascading triggers turned on. Several years ago we did look into turning this on and decided that the effort and potential problems more than outweighed any benefit.
Anyway, probably the best approach is using the update(status) approach. You might also want to look at using @@NESTLEVEL either as an extra safeguard or to use in troubleshooting. If @@NESTLEVEL exceeds a certain amount you could write to an error table information about the condition prior to exiting the trigger.
June 10, 2003 at 1:14 am
One (additional) way to avoid trigger recursion is using an INSTEAD OF trigger. You probably will have to rewrite your trigger since the original update will not be executed...
June 10, 2003 at 9:19 am
quote:
update trigger that when fired, updates the very same record that caused the trigger to fire. This was not allowed in SQL7.
This is news to me. I've been writing update triggers to update the updated record since v6.
It can be a pain to debug, since you have to work with the inserted and deleted tables, as well as the target table.
Some key points:
1) all assume that more than one record may be updated - that is, don't approach the problem with the assumption that only a single record will be updated. I see this problem all too often.
- a set based approach is best, but you can use a loop, or even a cursor if you need to.
[not that I like cursors]
2) When you update the target table, use a join on the inserted or deleted table, along with the appropriate WHERE conditions, to get at and update the target table.
As its name implies, the trigger fires after the action, in this case the update. But the commit does not take place until after the trigger completes.
update T
set mystatus row = 'flag'
from tbltarget as T
inner join inserted as I
where T.somefield = I.someotherfield
and etc.
What's the business problem you're trying to solve?
June 10, 2003 at 10:01 am
I second the suggestion to consider "instead of" triggers. I am currently adding / modifing some columns to the data that is being inserted and the "Instead of" trigger completely avoids the issues with re-firing the trigger and Nesting Levels.
June 10, 2003 at 11:35 am
a few gotchas I've run into in the field:
Joining inserted to deleted (for multirow compatibility) is indeed a good idea as long as you do NOT allow updates to your pk's. For that matter, there are very few instances (imho) that you would want to allow it. And for the cases for which you have no pk's be even more cafeful on your join condition.
One final point: In an update trigger, do NOT assume that there will be records in inserted and deleted. It is STILL possible to get caught into the dreaded "sp nesting level exceeded" on a self updating trigger that is well protected. In any update trigger that updates to itself with recursive trigger option enabled, it is normally a good idea to bail if the inserted count is zero. The reason for this is that the trigger will still fire, even if no rows are affected by the update statement, which of course will lead to the trigger nesting level problem.
Also, the @@nestlevel thing, while a good idea, can also cause you a bit of heartburn. In a distributed trigger environment, it can become possible to wind up firing a trigger which fires a trigger on another table, which then fires back on the originating table. If you have multiple triggers for an action, it gets even more complicated. Thus, if you use the @@nestlevel thing, be especially careful to ensure that you know your trigger firing scheme.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply