Deleting a Record in a View


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?


more ▼

asked Oct 16, 2012 at 09:57 PM in Default

avatar image

20 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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."

more ▼

answered Oct 16, 2012 at 10:45 PM

avatar image

15.6k 22 55 38

(comments are locked)
10|1200 characters needed characters left

better you can post the code how thesource table is same

more ▼

answered Oct 17, 2012 at 05:03 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2012 at 09:57 PM

Seen: 702 times

Last Updated: Oct 17, 2012 at 05:03 AM

Copyright 2018 Redgate Software. Privacy Policy