x

Deleting a Record in a View

Hello:

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?

back_in_the_1980s
more ▼

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

back_in_the_1980s gravatar image

back_in_the_1980s
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

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

(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

azarudhin gravatar image

azarudhin
0

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x65

asked: Oct 16, 2012 at 09:57 PM

Seen: 582 times

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