DML statement in a SQL Views

I am trying to 开发者_如何转开发create a view in that view I want to insert a record or update a record based on perticular condition so can we insert or update in SQL view. Can we have insert or update statement in view?

Short answer: Yes. But there are restrictions.

Eg: (taken from http://msdn.microsoft.com/en-us/library/ms180800(v=sql.90).aspx )

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:

1. An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP). 2. A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.

The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

Sooo... if it is a relatively straight forward insert or update you're trying to achieve, then it is very possible - but the more complex the query (and the view), the more difficult it becomes.

I'd also offer some advice against doing this if you can - in my opinion it adds an unnecessary layer of complication and (as someone who has had to unravel others queries that use this process) it is a real pain to maintain.

No you cannot have an insert or update statement in a view. Refer to the documentation for CREATE VIEW

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

It enforces that the definition must be a SELECT statement.

It would probably be better to instead use a stored procedure or function that returns a table.