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>
3 Solutions collect form web for “Unable to perform delete on View SQL Server 2005”
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.