T-SQL UPDATE statement affects more records than s

问题:

The following is driving me nuts, please help me!

As a check before running an UPDATE statement (T-SQL; SQL Server 2012) I run:

select  * 
from Treatment_Day42 
where td42pinit = 'J M' and td42pid = 'ADA';

and I get 1 row returned, as I expect.

However, running

update Treatment_Day42 set td42pid = 'ADA252' 
where td42pinit = 'J M' and td42pid = 'ADA';

affects four records, not one as in the select statement with the exact same WHERE clause. I get the row returned by the select plus 3 other rows where td42pid = ADA458, ADA671 and ADA658.

Why is the UPDATE affecting more rows than I intend it to?

Thanks.

Clemens

回答1:

A trigger on the table could do this. Have you checked for an existing trigger?

回答2:

If its not a trigger it could also be on update cascade. This however less common than triggers so I would check triggers first. If not check out your table and the column that you are updating.

回答3:

You must be having a trigger on your table which must be affecting more rows. You can check for trigger on a table by using

EXEC sp_helptrigger 'tablename';

For more options on sp_helptrigger, you can visit the below MSDN link

http://msdn.microsoft.com/en-us/library/ms189836.aspx

回答4:

I was facing the same issue, and then I checked, I had implemented a TRIGGER on myTable.

To check, if you having any trigger on your table or not, just try it:

EXEC sp_helptrigger yourTableName;