Double Results for a SQL Update Statement

Question

I've been scratching my head with this for a couple of hours already... This is what's going on:

I run this statement

SELECT STATUS FROM [dbo].[TMP_TEST_CALL_LIST] where identity = '659303186000000000'

and I get the following result (a single row):

'Y'

Then I run this statement

update [dbo].[TMP_TEST_CALL_LIST] set status='Z' where identity = '659303186000000000'

and I get THIS!!!:

(1 row(s) affected)

(1 row(s) affected)

As if 2 statements would have been executed!

But it gets worse... even if I run it like this:

update [dbo].[TMP_TEST_CALL_LIST] set status='Z' where identity = '659303186000000000' AND status ='Y'

it will give me the same "double" result. And if I run it with a WHERE clause that doesn't match any records it will tell me 0 rows(s) affected... twice.

The (even more) spooky thing is that this is happening to some tables in the DB, and not to others. I can't figure out what the differences between the tables are.

H E L P ! ! !

Thanks.

Note: this is SQL Server 2008 R2

Check if there's an update trigger on the table. Also, if you have show execution plan turned on, that results in an extra rowset with information being send to the client — Andomar, Mar 05 '12 at 17:29

score 6 · Accepted Answer · answered Mar 05 '12 at 17:42

Triggers are almost certainly the culprit, especially if you are only finding this behavior on certain tables in the database.

A trigger is code that is executed when a record is INSERTED, UPDATED, or DELETED (any or all of these). Typically triggers are used to implement business rules, write to audit tables etc. It's a convenient way to add this kind of behavior universally across the application without having to rewrite a lot of code. The downside is, of course, the lack of transparency. Triggers are not always obvious that they are running or even exist.

If you expand the tables tree in management studio, find one table that is behaving this way and expand it further. There will be a folder called triggers which you can expand to see any triggers defined on the table.

enter image description here

You can also execute a query to list triggers like this:

SELECT trigger_name = sysobjects.name, trigger_owner = USER_NAME(sysobjects.uid),table_schema = s.name, table_name = OBJECT_NAME(parent_obj),
  isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
  isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
  isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
  [disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') 
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
    INNER JOIN sys.tables t 
        ON sysobjects.parent_obj = t.object_id
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'

*image taken from this site: http://www.mssqltips.com/sqlservertip/1380/disable-triggers-in-sql-server-2005/

Yes!!! I have triggers on both tables that this is happening on! You are a genius. I'm using triggers to keep track of when what the last datetime the record was updated. Now another question: you said "culprit" - are there any risks on having triggers? Are there other ways of accomplishing that? — Amarundo, Mar 05 '12 at 17:57
Hi from 2020. I just took over an OLD database still in production. This answer is very relevant to me at the moment!! Thank you! — default_noob_network, Feb 07 '20 at 11:37

Double Results for a SQL Update Statement

1 Answers1