Update and rows affected

Question

Update and rows affected

Bristolred

Old Hand

Points: 394
More actions
February 2, 2010 at 11:48 am

#210502

Hi
This is driving me nuts. I've run the update command below to change the job status which is does ok, but why is it reporting that 3 rows have been affected when only 1 (the JOB STATUS row) has been updated.
UPDATE SY_JOB_REQUESTED
SET JOB_STATUS ='JOBSTAT09'
WHERE (CODE IN ('441417'))
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Either there are more rows than you think, or there are triggers on the table doing things behind the scenes.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bristolred Old Hand Points: 394 More actions · Answer 2

Thanks for replying.:-)

There is only 1 row for sure. So it must be a trigger most probably 2?

How can you find out if a table has any triggers on it? and what these triggers are called?

Thanks in advance.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 3

Find the table in question in SSMS, expand the node and you'll find a subnode labelled 'Triggers' underneath it. When you expand that one you'll see the names. From there you can right-click on script the trigger(s) in a new query window.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

S t e f SSC Eights! Points: 977 More actions · Answer 4

You can also query sys.triggers or sys.objects on type 'TR' or 'TA'

Bristolred Old Hand Points: 394 More actions · Answer 5

Thanks for the replies.

Easy when you know how:-)

I suppose it's the way the triggers have been created, but it would be nice for more information on the rows affected i.e.

1 row affected in table ??????

I row affected in table ??????

1 row affected in table ??????

or even ?????? row affected in table ??????

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 6

Just script out the T-SQL code of the UPDATE trigger into a query window and check out what it does? I'm pretty sure you'll see some INSERT/UPDATE/DELETE statements in there.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

SQL doesn't report the table affected, just the rows.

You could pretty easily modify the triggers so they would report what table they affect, but most often, you really don't need that.

Actually, most triggers should have "set nocount on" at the beginning of the code in them. They can otherwise end up causing problems with code that accesses the tables they are on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon