February 11, 2003 at 10:00 am
I have a simple view that joins two tables. I need to update the output of the view in a post process update. Should I do an update referencing the view or should I do the update breaking apart the two tables and updating each one independently? Does it matter?
February 11, 2003 at 10:32 am
In order to update view that has more than one base tables, you have to create partitioned view and there are rules for update a patitioned view. See BOL for details.
Quoted from BOL.
"Partitioned views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
Expect differences in behavior when working with updatable, partitioned views that have more than one table involved in DELETE, INSERT, or UPDATE statements."
If you like to update the tables individually, you need to place two update statements in one single transaction for data consistentcy.
February 11, 2003 at 5:25 pm
You could also use an instead of trigger to handle the updates in the view.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply