Unable To Perform Delete On View Sql Server 2005

I am unable to perform a delete on a View. Everything worked fine on the individual tables. EDIT1: Added Trigger CREATE TRIGGER myTrigger ON [ViewName] INSTEAD OF DELETE AS DELETE

Solution 1:

Okay, let's imagine one instance where this error will occur (since you haven't shown your view definition).

Let's assume we have a view:

CREATEVIEW dbo.V1
with schemabinding
asselect'T1'as TabName,T1ID as ID,ImportantDate from dbo.T1
    unionallselect'T2',T2ID,ImportantDate from dbo.T2

is we now attempt:

DELETEfrom dbo.V1 where ImportantDate < DATEADD(day,-90,CURRENT_TIMESTAMP)

we'll get the error you've shown (or similar). So what we need is a trigger:

CREATETRIGGER T_V1_D
on dbo.V1
instead ofdeleteasset nocount ondeletefrom dbo.T1 where T1ID in (select ID from deleted where TabName ='T1')
    deletefrom dbo.T2 where T2ID in (select ID from deleted where TabName ='T2')

This trigger gets considerably more complex to write if there's no easy way to correlate rows from the deleted psuedo-table with which rows need to be deleted from each base table.

Solution 2:

DELETE command: http://msdn.microsoft.com/en-us/library/ms189835.aspx

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

CREATE VIEW command, Updatable Views: http://msdn.microsoft.com/en-us/library/ms187956.aspx

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

Solution 3:

Are you referencing columns from multiple tables? If so that error sounds pretty straight forward.

You can however, use a stored procedure instead of a view to execute this.