I was always under the impression that deleting a record in a view deletes the corresponding record in the underlying source table. But, today, someone demonstrated deleting a record in a view and it did not delete the record in the source table.
How can that be? Does it depend on how the view was programmed, ultimately?
asked Oct 16 '12 at 09:57 PM in Default
I would be interested in seeing the code used for this demonstration including the object definitions....
Views without triggers
First, there are several conditions on whether a view can be used to alter the data in the underlying table at all. But if those conditions are not met, you should receive an error rather than have the change appear to go through without doing anything. If you successfully execute any kind of change to the data (update, insert, delete) on a view, it should change the data in the underlying tables.
Also, normally you can only affect one table at a time this way. You can't for instance do an insert on a view (without a trigger, we'll get to that next) and expect to have it populate two tables.
Views with triggers
But views that have "instead of" triggers on them can be different. They don't need to meet all the conditions since the triggers is essentially intercepting the statement and changing what happens. In this case, just about anything that a trigger can do could happen.
One common use for this is to have an insert statement go ahead and populate two base tables.
It can also do things like report success while changing nothing if it wants to...
Edit: There is a reference for this on MSDN. Look down towards the section on "Updateable views."
better you can post the code how thesource table is same
answered Oct 17 '12 at 05:03 AM