returned row count when a trigger is invoked

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy Fish - 06 Mar 2008 18:12 GMT

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

Reply to this Message

Mufaka - 06 Mar 2008 19:16 GMT

> Hi,
>
[quoted text clipped - 15 lines]
>
> Andy

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

Reply to this Message

Jim Rand - 07 Mar 2008 13:49 GMT

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

-- interfering with SELECT statements.

SET NOCOUNT ON;

Reply to this Message

William Vaughn - 07 Mar 2008 00:46 GMT

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

Signature

__________________________________________________________________________
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)
____________________________________________________________________________________________

> Hi,
>
[quoted text clipped - 15 lines]
>
> Andy

Reply to this Message

Cor Ligthert[MVP] - 07 Mar 2008 05:04 GMT

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

Reply to this Message

Andy Fish - 07 Mar 2008 09:44 GMT

> Andy,
>
[quoted text clipped - 10 lines]
> 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

> Cor

Reply to this Message

Andy Fish - 07 Mar 2008 12:00 GMT

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

> Hi,
>
[quoted text clipped - 15 lines]
>
> Andy

Reply to this Message

returned row count when a trigger is invoked

Free Magazines