We help IT Professionals succeed at work.

What is danger of DML thru a view?

Eric3141
Eric3141 asked
on
At some class I attended years ago I was told that update, insert, or delete through a view was not a wise thing to do.  I am aware that I can use a trigger to prevent this or I can include a GROUP BY clause in the view definition and include every column in the Select statement to prevent it.  Before I do this I want to ask what the dangers are and what your opinion and experience has been.

Also, if I allow UPDATE but disallow Insert and Delete is that acceptable in your opinion?

Thx in advance.

Comment
Watch Question

Top Expert 2011
Commented:
You can insert, update, delete from a view. However, there are limitations.

Please read this before procedding:

http://www.craigsmullins.com/cnr_0299b.htm

Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
There is nothing inherently wrong with using DML thru a view.  SQL Server itself will insure that the DML affects only a single table, and that the row from that table to be modified is uniquely identified.

Author

Commented:
Did this used to be a problem in SQL Server 2000?  It seems like years ago I deleted a row in a view and deleted all rows in both parent and child table with the one row deleted in view.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.