November 3, 2009 at 5:44 pm
I have a proc that updates two tables. Table tOrderInfo has an Update Trigger on it.The trigger is set to rollback if certain criteria is met. When this happens it reversed the invoking proc's update, however because I am looking for a rowcount in this proc to commit the transaction the first update still commits.
I do a lot of error handling/rollbacks based on Rowcount and now I realize it isn't always the best thing to use. Any ideas? thoughts? TIA!
BEGIN TRAN
update tOrder...
SET @RowCount = @@RowCount
IF isnull(@RowCount,0) = 0
BEGIN Print 'Update Failed' Rollback Tran RETURN END
update tOrderInfo... (Table with Trigger)
SET @RowCount = @@RowCount
IF isnull(@RowCount,0) = 0
BEGIN PRINT 'Update Failed' Rollback Tran RETURN END
IF @RowCount = 1 BEGIN PRINT 'Update a Success' COMMIT TRAN END
November 4, 2009 at 7:55 am
I'm not sure I'm clear on what exactly you're trying to accomplish with this.
If the first update statement updates 1 or more rows, but the second one doesn't update anything, do you want the first update to commit or to roll back?
The way it's currently written, it looks to me like it will roll back both if the second one fails to update anything. Is that the desired behavior?
- 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
November 4, 2009 at 8:51 am
Yes if the second update fails it will Rollback the first update.
When the trigger that fires on the second update does a rollback (reversing the update) the RowCount for that second update command still returns a positive value thus commiting the transaction. This leaves the first update committed but the second one uncommitted. Make sense?
I didn't know if there was some other value or something that could be used to see if the update is truely commited to that table.
November 4, 2009 at 9:03 am
Can the code from the trigger be moved into the proc?
- 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
November 4, 2009 at 9:15 am
I actually "fixed" this issue already but I was looking for another way to do it using this logic. Using Rowcount to verify commited changes can be a faulty way to do things.
For arguments sake though we could move the trigger code into the proc however the reason I created a trigger is I am unaware of all the sources that could change that table and I needed to ensure data consistancy.
November 4, 2009 at 9:21 am
Makes sense.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply