SCENARIO 1:
If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.
However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:
To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.
You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.
SCENARIO 2:
If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.
However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:
Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.
You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.
SCENARIO 2:
No comments:
Post a Comment