SQL Server Database - Allowing application to edit records in a view?

Question

I made a custom view for a group of three tables. How would I configure the view so that it can be edited by an application using it like a table? I am using SQL Server Studio Express.

Answer 1

You'll need to create INSTEAD OF INSERT and INSTEAD OF UPDATE triggers on the view and then write insert and update statements within the trigger to manipulate the data in the 3 underlying tables. See this MSDN article that has a simple example. You'll find the desired edited values in the special INSERTED table within the context of the trigger definition.

Answer 2

Views in SQL Server can be updatable, but there are restrictions (CREATE VIEW, Updatable View section):

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

Otherwise, you'll have to use INSTEAD OF triggers.