locked
Views and Instead of Update Trigger Limitation. Why? RRS feed

  • Question

  • I am attempting to use views and Instead of triggers to insert, update and delete rows from THE table that is exposed through a view.  In my application, the views provide a filtered set of rows from the base table that a user has permission to access. To improve performance, I submit a set (i.e. multiple rows) to be acted upon in a single SQL Statement. The insert and delete triggers work great. But, for some reason, the Instead of Update trigger on the view will not accept multiple rows. The Error I receive is:

     

    Msg 414, Level 16, State 1, Line 1
    UPDATE is not allowed because the statement updates view "FooView" which participates in a join and has an INSTEAD OF UPDATE trigger.

     

    The only hint I can find about this behavior is from BOL for the Transact-SQL Update statement:

    "A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause."

     

    Why is this not allowed?

     

    If the trigger is on a table and the update statement is executed against the table it works. Why is the statement not allowed to execute against a view that has exactly the same construction as the table?

     

    Any help or explanations for this behavior is appreciated.

     

    I've included a code sample that will replicate the behavior and problem.

     

    ----------------------------------------------

    Code Sample:

     

    CREATE TABLE Foo (
      ID INT PRIMARY KEY,
      aName VARCHAR (10),
      aValue FLOAT
    )

    GO
    -- The view and its triggers

    CREATE VIEW FooView AS
      SELECT [ID]
          ,[aName]
          ,[aValue]
      FROM Foo

    GO

    -- Instead of Triggers on the view
    CREATE TRIGGER FooViewInsertInsteadOf
       ON  FooView
       INSTEAD OF INSERT
    AS
    BEGIN
        INSERT INTO Foo
        SELECT Id, aName, aValue
        FROM INSERTED
    END
    GO

    CREATE TRIGGER FooViewInsteadOfUpdate
       ON  FooView
       INSTEAD OF UPDATE
    AS
    BEGIN
        UPDATE Foo
        SET
       Foo.aName = Inserted.aName,
       Foo.aValue = Inserted.aValue
        FROM Foo
        JOIN INSERTED on INSERTED.ID = Foo.Id
    END

    GO

    -- Test the Insert Trigger
    INSERT INTO FooView
    SELECT     2, 'First',  2.0
    UNION ALL
    SELECT     3, 'First',  3.0
    UNION ALL
    SELECT     4, 'First',  4.0
    GO

    SELECT * FROM FooView
    GO

    -- Update a single row in the view. This works with the Instead of Update Trigger.
    UPDATE FooView
     SET
     FooView.aName = 'Third',
     FooView.aValue = 3.3
     WHERE FooView.ID = 3

    GO

    SELECT * FROM FooView
    GO

     

    ===============================

    -- T-SQL will not support the following statement when executed against a view with an instead of trigger.
    -- If this statement is executed against a table with an instead of trigger it works as desired.
    -- Why can't I execute this against the view?

    UPDATE FooView
     SET
     FooView.aName = tmp.f1,
     FooView.aValue = tmp.f2
    FROM FooView
    JOIN (
     SELECT  2 as [id], 'Second' as [F1], 2.2  as [F2]
     UNION ALL
     SELECT  4, 'Fourth', 4.33
    )
    as tmp on FooView.ID = tmp.[id]

     

    Wednesday, September 19, 2007 5:08 PM

All replies

  • Bump.

     

    Can somebody from Microsoft provide feedback to this thread?

     

    Tuesday, September 25, 2007 6:25 PM
  • I am facing a similar problem. Somebody please help....

    Wednesday, September 26, 2007 12:25 PM
  • Ok; so it has now been nearly 6 years since this post and there is no fix for this even with the newer versions of SQL server including 2012. Is there anyone who can address this please?

    Thanks

    Wednesday, July 3, 2013 11:02 PM
  • Just rain into this myself, as I'm using Views with INSTEAD OF triggers to store data into a genericized common table.  It's a neat way to have 'custom' tables for different clients, without having to add tables to your schema.

    We found that UPDATE...FROM just won't work, throwing the error you found.   I researched, and found an excellent article by Hugo Kornelis on sqlblog.com:

    Let's deprecate UPDATE FROM!

    and found that UPDATE...FROM isn't ANSI (we know), but it also isn't idempotent.     Instead we can use MERGE, and it provides the same UPDATE...FROM functionality, in an ANSI compliant, idempotent way.  AND, it solves my problem, MERGE works for doing updates against a View with INSTEAD OF triggers.  Yeah!

    • Proposed as answer by Naomi N Friday, August 7, 2015 7:27 PM
    Friday, August 7, 2015 1:00 PM