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
FROM [ViewName]
WHERE [ColumnName] < DATEADD(Day, -90, GETDATE())

I got the following error before adding a trigger

View or Function "blah" is not updateable because the modification affects multiple base tables>

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:

CREATE VIEW dbo.V1
with schemabinding
as
    select 'T1' as TabName,T1ID as ID,ImportantDate from dbo.T1
    union all
    select 'T2',T2ID,ImportantDate from dbo.T2

is we now attempt:

DELETE from 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:

CREATE TRIGGER T_V1_D
on dbo.V1
instead of delete
as
    set nocount on
    delete from dbo.T1 where T1ID in (select ID from deleted where TabName = 'T1')
    delete from 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.

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.

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.