returned row count when a trigger is invoked

[Andy Fish]

Hi,

in my .net application, I am using IDbCommand.ExecuteNonQuery() to update a
single row. as a matter of good programming practice, I check the "rows
affected" return value to verify that only 1 row was updated.

the problem comes if there is a database trigger on the table which does
further updates. it seems to add the number of rows updated by the trigger
to the rows affected by the original SQL and return me the total. I would
like to ignore the count of rows updated by the trigger.

is there any way I can either (a) code the trigger so that it doesn't affect
the value returned to my program; or (b) get more information about how the
"rows updated" count is comprised (to distinguish between rows affected by
the original SQL and those affected by the trigger)

Many thanks in advance for any clues

Andy

 

[Mufaka]

Hi,

in my .net application, I am using IDbCommand.ExecuteNonQuery() to
update a single row. as a matter of good programming practice, I check
the "rows affected" return value to verify that only 1 row was updated.

the problem comes if there is a database trigger on the table which does
further updates. it seems to add the number of rows updated by the
trigger to the rows affected by the original SQL and return me the
total. I would like to ignore the count of rows updated by the trigger.

is there any way I can either (a) code the trigger so that it doesn't
affect the value returned to my program; or (b) get more information
about how the "rows updated" count is comprised (to distinguish between
rows affected by the original SQL and those affected by the trigger)

Many thanks in advance for any clues

Andy

Assuming this is SQL Server, I think that you will have to use
@@ROWCOUNT as a return value or an output parameter.

 

[William Vaughn]

Let your logic fake the RowCount by selecting a single row from a dummy
table post successful update?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Andy,

I am currious about that so maybe I can learn something from it.

as a matter of good programming practice, I check the "rows affected"
return value to verify that only 1 row was updated.

This kind of text let me always think at the time of the flexible disk, when
I knew a guy who put that always in the air to see if there was a hole in
it. That was based on the fact that he was doing that as good practice as
well with punch cards.

I make always my architecture in a way that there can only be one row
updated.

Cor

 

[Andy Fish]

Andy,

I am currious about that so maybe I can learn something from it.


This kind of text let me always think at the time of the flexible disk,
when I knew a guy who put that always in the air to see if there was a
hole in it. That was based on the fact that he was doing that as good
practice as well with punch cards.

I make always my architecture in a way that there can only be one row
updated.

I have also made the architecture such that there can only be one row
updated (that's why this error has never been thrown before on any of my
100+ server installations). however, a programming error in the architecture
(or a bug in the database, or even a hardware/network fault) could cause
invalid results.

that's why programmers use asert statements - to verify entry and exit
conditions. just because none of my assert statements has ever triggered in
production, that doesn't mean it was a waste of time putting them in.

Andy

 

[Andy Fish]

thanks for the replies

I was after a solution that would allow me to check the rowcount of rows
updated by the direct statement without including the rows updated by the
trigger, but that would work even if there was no trigger (since this is the
99% case)

I still haven't found a way to deconstruct the rowcount (see my later post).
however, I did discover the "set nocount on" command. putting this inside
the trigger will stop the trigger's row counts being returned to the caller

FYI )

andy

 

[Jim Rand]

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;