update commands on a view inside a transaction
-
amos-870870 (12/5/2011)
Reverse the updates?? Is there some tactick you refer to?This was done to 1 test record in a test DB before I unleashed it on my live DB, if that is what you were refering to.
Yea that was what I was thinking originally, using temp tables, I thought there would be a more efficient way of accomplishing this. I guess I was wrong.
I meant do update #2 first (the one that doesn't "remove" data from the view).
I'd personnally create a proc for this or maybe hide the logic in an instead of trigger. I don't know which one is best as I've not toyed with this.
I remembered that in access you had to specify which table was the updatable one but that's the extent of my experience here.
I'd try to see what would be the consequence of using repeatable reads isolation level here. Maybe the data would still be available in the 2nd update).
That's just a guess based on theory but if you do both updates in the same begin / commit tran it might work.
-
Yea I used the view because I thought maybe I could update the two tables that way.
You know what the problem was, I was using references that were outside SQLServerCentral.
-
amos-870870 (12/5/2011)
You know what the problem was, I was using references that were outside SQLServerCentral.No sure what you mean by this?
-
unfortunatly ether/or table that gets updated will remove them from the view.
-
amos-870870 (12/5/2011)
unfortunatly ether/or table that gets updated will remove them from the view.I'd still try repeatable reads in a single transaction... just out of curiosity ;-).
After that just save the keys you need to update and run both updates directly to the base table. No need to waste any more time on this! :hehe:
-
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
You know what the problem was, I was using references that were outside SQLServerCentral.No sure what you mean by this?
What I mean is SQLServerCentral has a great community, and has been a source of accurate information. I got the information about using the view to update 2 tables from somewhere else.
-
amos-870870 (12/5/2011)
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
You know what the problem was, I was using references that were outside SQLServerCentral.No sure what you mean by this?
What I mean is SQLServerCentral has a great community, and has been a source of accurate information. I got the information about using the view to update 2 tables from somewhere else.
:-D.
It's not like 100% of the content here is 100% right either :hehe:.
-
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
You know what the problem was, I was using references that were outside SQLServerCentral.No sure what you mean by this?
What I mean is SQLServerCentral has a great community, and has been a source of accurate information. I got the information about using the view to update 2 tables from somewhere else.
:-D.
It's not like 100% of the content here is 100% right either :hehe:.
Heheh, true enough. I've managed a few foot in mouth moments myself. However, as a workaround, use an OUTPUT on the update to dump the changed records (and keys needed to find the items in the secondary tables) into a #tmp and then work from there afterwards.
Nice idea on the multi-table update, but what you can't do against schema isn't going to happen later, either, as a general rule.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
unfortunatly ether/or table that gets updated will remove them from the view.I'd still try repeatable reads in a single transaction... just out of curiosity ;-).
I have not used Repeatable reads before, but that sounds like the solution I was looking for, in lue of the output. Whether I use it or not are there any comments on repeatable reads that anyone would like to add?
-
amos-870870 (12/5/2011)
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
unfortunatly ether/or table that gets updated will remove them from the view.I'd still try repeatable reads in a single transaction... just out of curiosity ;-).
I have not used Repeatable reads before, but that sounds like the solution I was looking for, in lue of the output. Whether I use it or not are there any comments on repeatable reads that anyone would like to add?
A transaction wrapper under Read Committed would be equivalent here, as long as the update occurred first. Repeatable Read and Serializable are more for when you're doing selects before you start doing updates.
If you're going to try to lock all involved records up before you start, swap over to repeatable and do a select of all the records via the view (dump it into a #tmp if you like) to force locks on all the records first. You might do it with an UPDLOCK hint to make sure you get exclusive access to all the necessary records.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
In the end I used the straight forward use of a temp table to use as a selection source to update my 2 tables.
Thanks for all the info.
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply