none
"instead of update" trigger on view ok in ssms query and debug, but not in view (duplicate rows) RRS feed

  • Question

  • I may be overlooking the obvious here, since I rarely use triggers.  I have a view with an update trigger that works perfectly fine when being debugged, or when an update is performed on it by typing in the SSMS query window.  For example, the following statements all work fine when debugging step by step or when typed in the SSMS query window:

    UPDATE viewFoo SET fldOne=13, fldTwo=15 WHERE dtm = '2011-08-05 17:50';
    UPDATE viewFoo SET fldOne=21 WHERE dtm = '2011-08-05 17:50';

    However, when I open the view table for editing directly in SSMS and try to enter the value manually for that row (where dtm='2011-08-05 17:50'), I get this error:

    No row was updated.  The data in row 9360 was not committed.
    Error Source: Microsoft.SqlServer.Management.DataTools.
    Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(33 rows).  Correct the errors and retry or press ESC to cancel the change(s).

    The view is a pivoted (crosstab) transform of a normalized table.  The columns in the view are: dtm, fldOne, fldTwo, fldThree etc.  (the number '33' is the total number of columns in the view table, including, oddly, dtm.)  In the source table (BaseTable) that gets updated, the fields are: dtm, xId, Price.   

    The instead of update trigger on the view is this:

    INSTEAD OF UPDATE
    AS
    BEGIN 
     UPDATE dbo.BaseTable
     SET Price=
     CASE b.xId
      WHEN 4 THEN [i].[fldOne]
      WHEN 12 THEN [i].[fldTwo]
      WHEN 16 THEN [i].[fldThree] 
    [and so on for the 32 columns in the view exclusive of dtm]
     END
     FROM dbo.BaseTable AS b
     JOIN INSERTED AS i ON b.Dtm = i.Dtm
     WHERE xId In (4, 12, 16, [and all the 32 distinct values given above]); 

    It's not easy to debug these triggers, but as far as I've been able to (using the update query that works fine above), there should be no problems, I think.  Inserted has one row (dtm, fldOne, fldtwo, etc.) and the join between inserted i and the base table b results in 32 rows (with each row having: b.dtm, b.xId, b.Price, i.dtm, iFldOne, i.FldTwo etc.), but with a distinct xId in each row, so I expect update to succeed, as it does from the ssms query window.  But there's some mistake I've made here.

    Saturday, August 6, 2011 11:11 PM

Answers

  • The SSMS edit window checks to be sure that you only update the correct number of rows it thinks you are going to update.  So, for example, if you change one row, it looks for the DONE_IN_PROC message that says only one row is updated.  (The DONE_IN_PROC message is the one SSMS uses to generate the "N row(s) affected" messages).

    Since your trigger does not have a SET NOCOUNT ON, when the trigger is fired, it sends a DONE_IN_PROC message telling how many rows were updated in the BaseTable.  That message arrives first, followed by the message of how many rows were affected in the view.  Since a change in the view affects many rows in the table, and since SSMS only looks at the first DONE_IN_PROC message, it thinks the wrong number of rows were updated, so you get that error.  The solution is to add a SET NOCOUNT ON at the beginning of your trigger, so something like

     

    INSTEAD OF UPDATE
     AS 
    BEGIN 
    SET NOCOUNT ON
     UPDATE dbo.BaseTable
     SET Price=
     CASE b.xId
     WHEN 4 THEN [i].[fldOne]
     WHEN 12 THEN [i].[fldTwo]
     WHEN 16 THEN [i].[fldThree] 
     [and so on for the 32 columns in the view exclusive of dtm]
     END
     FROM dbo.BaseTable AS b
     JOIN INSERTED AS i ON b.Dtm = i.Dtm
     WHERE xId In (4, 12, 16, [and all the 32 distinct values given above]); 
    

    Tom

     


    • Marked as answer by TechVsLife2 Sunday, August 7, 2011 1:52 AM
    • Edited by Tom Cooper Sunday, August 7, 2011 3:33 AM
    Sunday, August 7, 2011 12:33 AM

All replies

  • The SSMS edit window checks to be sure that you only update the correct number of rows it thinks you are going to update.  So, for example, if you change one row, it looks for the DONE_IN_PROC message that says only one row is updated.  (The DONE_IN_PROC message is the one SSMS uses to generate the "N row(s) affected" messages).

    Since your trigger does not have a SET NOCOUNT ON, when the trigger is fired, it sends a DONE_IN_PROC message telling how many rows were updated in the BaseTable.  That message arrives first, followed by the message of how many rows were affected in the view.  Since a change in the view affects many rows in the table, and since SSMS only looks at the first DONE_IN_PROC message, it thinks the wrong number of rows were updated, so you get that error.  The solution is to add a SET NOCOUNT ON at the beginning of your trigger, so something like

     

    INSTEAD OF UPDATE
     AS 
    BEGIN 
    SET NOCOUNT ON
     UPDATE dbo.BaseTable
     SET Price=
     CASE b.xId
     WHEN 4 THEN [i].[fldOne]
     WHEN 12 THEN [i].[fldTwo]
     WHEN 16 THEN [i].[fldThree] 
     [and so on for the 32 columns in the view exclusive of dtm]
     END
     FROM dbo.BaseTable AS b
     JOIN INSERTED AS i ON b.Dtm = i.Dtm
     WHERE xId In (4, 12, 16, [and all the 32 distinct values given above]); 
    

    Tom

     


    • Marked as answer by TechVsLife2 Sunday, August 7, 2011 1:52 AM
    • Edited by Tom Cooper Sunday, August 7, 2011 3:33 AM
    Sunday, August 7, 2011 12:33 AM
  • Tom, 
    That exactly solved the problem, I must have accidentally deleted the SET NOCOUNT ON.  It wouldn't have occurred to me that the absence of that line would cause a runtime error (I've heard of it only in connection with performance issues), so it wouldn't have been one of the things I would have looked out for or tested, at least not after a lot of wasted time.  but the time I saved was paid by the expense of your time (and on a Saturday night!), so thank you. 

    Sunday, August 7, 2011 2:08 AM