ExecuteNonQuery() on a table with a trigger

Question

Post reply

ExecuteNonQuery() on a table with a trigger

Carl B.

SSCertifiable

Points: 7931
More actions
October 27, 2005 at 1:25 pm

#68501

Hello everyone,
This is a weird question... I know. But it is related to the way we are doig things in our apps.
Here it is:
When I call ExecuteNonQuery() to do an update on one row in the database, the code automatically verify that only one row have been affected by using the return value of the ExecutNonQuery() method.
But if I add a trigger that insert a row in an audit table, the same update will return that 2 rows have been affected.
Is there a way, in the trigger, that I can workaround that behavior?
Does this method use the @@rowcount variables?
Regards,
Carl

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe you could add the @@rowcount logic in the procedure after the update...

Also why do you want to trigger to do work on only one row at the time?

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Hello Remi,

You confirm me that the rturn value of the ExecuteNonQuery() is affected by the variable @@rowcount? Or is it just a guess?

For your question the answer is: It is not that we want the trigger to do work on a single record. It is that in that particular case we know that we must update a single row and we verify that in our app. The trigger is a client customization that was add to do auditing.

Regards,

Carl

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ok, can you run this code in the proc... that'll answer your question :

Update statement

print @@rowcount

then run from QA and see if you have 1 or 2 in there... if you get 2, then try disabling the trigger before running the update to make sure that the proc is actually updating only 1 row.

Carl B. SSCertifiable Points: 7931 More actions · Answer 4

Hello Remi,

Yesterday, I've found the solution :

In the trigger I just put SET NOCOUNT ON | OFF embeding the insertion in the audit table and it solve the problem.

Here is a dummy exemple:

CREATE TRIGGER [AFT_UPD] ON [dbo].[abs]

AFTER UPDATE

AS

SET NOCOUNT ON

insert into audit_abs select *, 'N', 'M' from deleted

SET NOCOUNT OFF

Thank's for your help,

Best regards,

Carl

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Now I rememember why this is considered a best practice .

Carl B. SSCertifiable Points: 7931 More actions · Answer 6

It is a "best practice"? I did not know... But it make sense.