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

  • Getting count of related tags
  • SQL Query to get latest price
  • how to store multi row query result in a variable in SSIS
  • Issue with xp_cmdshell
  • How to return default value from SQL query
  • SQL Server: if I don't specify a RECOVERY mode for a RESTORE DATABASE, what is the default?
  • CREATE TRIGGER myTrigger
    ON [ViewName]
    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
        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:

    on dbo.V1
    instead of delete
        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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.