deadlock by 2 triggers on same table

Question

deadlock by 2 triggers on same table

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

KBSK (7/1/2010)
Jeff Moden (7/1/2010)
GilaMonster (6/30/2010)
Jeff Moden (6/30/2010)
... but don't mind giving a little advice on what to change. 😉
The entire thing? 😉
Heh... z'actly. 😛
Hi All,
thanks for your valuable suggestions.
i know cursors are bad, but here the situation is that there are many things that are being done inside the loop.
Can all of them be written as set based query?
(I read the article 15ways to lose cursors)
I am eager to have your advice on some of the major flaws in this trigger.
(one of the thing was several update stmts are turned into single update stmt using CASE stmt - by Waynes.....thank you)
Thanks,
KB

The best thing that you could do here is to figure out how to do just one of those things to [font="Arial Black"]all [/font]the rows affected by the insert (or whatever). Then, add on other things. Think of what you want to do to whole columns instead of all the things you want to do to a single row. Once you adopt that paradigm shift, most of what you need to do in this trigger will become a whole lot easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Third time this morning - but yet again we have a situation where a professional can help out TREMENDOUSLY. Refactoring cursors into set-based operations can be EXCEEDINGLY difficult for those not used to doing so. Hire a pro to help you with this while simultaneously mentoring you on how he/she goes about the business. win-win-win.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service