Uninstallation failed in SQL Server 2005 view

Question

I can not perform the deletion in the view. Everything works fine on separate 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> 

Answer 1

Well, imagine one instance where this error will occur (since you did not specify your definition of the definition).

Suppose we have a representation:

 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 

we are now trying:

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

we will get the error you specified (or similar). So we need 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 becomes much more difficult to write if there is no easy way to match rows from the deleted psuedo table with which rows must be deleted from each base table.

Answer 2

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

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

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

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

Answer 3

Are you referring to columns from multiple tables? If so, the error sounds pretty straightforward.

However, you can use a stored procedure instead of a view to execute it.