locked
SQL SERVER 2005 INSTED OF Trigger RRS feed

  • Question

  • Hi.
    I set an INSTED OF INSERT trigger on a table, that deny the addition of rows in some conditions.

    My question is:
    When I run the insert command, I get the (X row(s) affected) message, even if X isn't the real number of added rows.

    I tried to set the @@ROWCOUNT function, but it turns out this isn't the way.
    Is there any way to change the affected rows count of a trigger / sp?

    Thanks,
    Yaakov
    Thursday, June 23, 2005 6:10 PM

Answers

  • This is by-design behavior.  @@ROWCOUNT reports the # of rows affected by the original DML statement.  If you think about it, an INSTEAD OF trigger could alter rows in many other tables and there would be no way to report an accurate rowcount.

    Your application will have to keep track of the # of rows.

    Thanks
    Friday, June 24, 2005 4:55 PM

All replies

  • This is by-design behavior.  @@ROWCOUNT reports the # of rows affected by the original DML statement.  If you think about it, an INSTEAD OF trigger could alter rows in many other tables and there would be no way to report an accurate rowcount.

    Your application will have to keep track of the # of rows.

    Thanks
    Friday, June 24, 2005 4:55 PM
  • Well, when your DML violates FK constraint for example, an error is raised, and you get a real @@ROWCOUNT.
    IMO, because INSTED OF triggers overrides constraints, there should be a way to modify the rowcount. In such a way, INSTED really becomes INSTEAD.

    BTW, there is a way to do so in stored procedures?

    Thanks,
    Yaakov

    Saturday, June 25, 2005 9:15 AM
  • I think the @@rowcount value you get after an INSTEAD OF trigger is actually the number of rows that might have been changed by the original DML statement if the trigger didn't exist. For example, an INSTEAD OF UPDATE trigger with a body that consist of

    begin return end

    will never do an update, but you could still get a number of rows reported as changed.

    At the start of the INSTEAD OF trigger , the @@rowcount value will be the number of rows that might have been changed by the original DML (the "external DML" value). If rows are changed within your trigger code, @@rowcount will be updated appropriately, but the value seems to be reset on exit from the trigger back to the "external DML" value.

     

    Tony

     

     

    Monday, August 28, 2006 8:50 PM