December 15, 2009 at 9:24 am
I have table A with trigger A1 that inserts a row into table B.
Table B has trigger B1 which performs some calculations and updates a row in table C.
I wish to add code in trigger A1 that will read the updated value in table C and act on it.
So does trigger A1 stop and wait for trigger B1 to complete when A1 inserts the row into table B?
Thank you in advance for any information that you can provide.
BTW, I'm trying to avoid creating a separate trigger and specifying first/last execution.
December 15, 2009 at 9:32 am
Are you doing an exercise aimed at creating deadlocks? That's the result you'll get.
The original transaction can't complete till Trigger A completes. Trigger A can't complete till Trigger B completes. Trigger B can't complete till Trigger C completes. Trigger C can't start till Trigger A completes (row locks). Since Trigger C can't complete (it can't even start), the whole thing will deadlock and roll back, including the original update that started it all.
You'll need to do something differently. Since I don't know your business requirements, I can't recommend anything specific. Quite possibly, Table C or Table B could be replaced by an indexed view, which would include the calculated column(s) for Table A, and you could get rid of all of the triggers and one or two tables. Hard to say if that applies in your case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2009 at 10:07 am
YIKES! I hate to duplicate the logic but I may need to do that in order to avoid potential deadlocks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply