Trigger increments rows affected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger increments rows affected

Author

Topic

dve
Starting Member

19 Posts

Posted - 2006-01-13 : 10:18:31

Hi,

An UPDATE STATEMENT executes a trigger which updates some other table.

How many rows are affected ? You would say that only 1 row is affected but SQL Server Management Studio returns:

(1 row(s) affected)

(1 row(s) affected)

which is then returned to .NET as 2 rows affected...

Is there anyway to change this. So that when I ask how many rows are affected it only says 1 ??

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 10:34:57

try putting "set nocount on" at the top of the trigger code to suppress the rowcount from the trigger's updates to the other table.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 10:46:45

Looks like this is the effect of either replication, or a trigger, updating "something else" in response to the initial update.

If you turn off the warning you won't get any indication of ONE or ZERO rows update, but you could do:

SET NOCOUNT ON
UPDATE MyTable SET MyColumn = 'FOO' WHERE MyColumn = 'BAR'
SELECT [RowCount] = @@ROWCOUNT

and then process the RecordSet with the [RowCount] column to check how many rows were updated.

Kristen

dve
Starting Member

19 Posts

Posted - 2006-01-13 : 11:04:18

Thanks TG,

SET NOCOUNT ON seems to do the trick!

Kristen,

what do you mean by you won't get any indication of ONE or ZERO rows update ?

Kristen
Test

22859 Posts

Posted - 2006-01-16 : 05:48:11

"what do you mean by you won't get any indication of ONE or ZERO rows update ?"

You asked for:

"So that when I ask how many rows are affected it only says 1"

if you use

SET NOCOUNT ON

you won't get ANY count back of how many rows were updated, so you'd need to put some logic into the SQL statement to return that Count to you (as per my example)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources