Post reply

Update and rows affected

  • 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)

  • February 2, 2010 at 11:49 am

    #1112452

    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

  • February 3, 2010 at 2:13 am

    #1112706

    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.

  • February 3, 2010 at 5:27 am

    #1112771

    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]

  • February 3, 2010 at 5:49 am

    #1112781

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

  • February 3, 2010 at 6:42 am

    #1112823

    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 ??????

  • February 3, 2010 at 6:53 am

    #1112830

    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]

  • February 3, 2010 at 7:51 am

    #1112901

    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

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

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