SQL SERVER 2005 INSTED OF Trigger

Hot Topic

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?
Adding values to DropDownList populated by Query
SSIS Portability
cube measure only displays one total
Web Assistant Wizard?
Running Create Replication Script
compress spaces
HTTP access to Analysis Services 2005 ?
Remote Connections
Use of user defined in SELECT clause

SQL Server

Server setup.
Cannot create a connection to data source
Restore a database from Yukon to SQL 2000 SP4 edition?
Custom destination component development sample?
Retrieving a rowset from a SSAS drillthrough action
efficient JOIN query
Conditional MDX ?
System Configuration Check
Problem with MsDtsSrvr.ini.xml config
Modify SSIS package saved on server

SQL SERVER 2005 INSTED OF Trigger

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

Answer this question

SQL SERVER 2005 INSTED OF Trigger

  • Ata Giray

    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

  • Genja_Eugeny

    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

  • A2324_1

    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


  • SQL SERVER 2005 INSTED OF Trigger
Answer this question