Get only result of update query

Question

When I execute an update on my SQL Server database, I get a result of the rows affected by the update AND the affected rows of some triggers.

So for example, an update executed directly on database:

UPDATE: (32 row(s) affected)
Trigger1: (1 row(s) affected)
Trigger2: (2 row(s) affected)
...

Now when I execute _context.Database.ExecuteSqlCommand(query, params) I always get the sum of all those results, in my example the result value is 35.

I only need the result of the UPDATE, in my example 32.

Is there any possibility to ignore the results of the triggers?

score 3 · Answer 1 · answered Jul 25 '14 at 12:47

3

Put SET NOCOUNT ON on the first line of your triggers.

answered Jul 25 '14 at 12:47

Alireza

4,976
1
23
36

it's not possible for me to change the triggers, otherwise it would be a nice solution. – Obl Tobl Jul 25 '14 at 12:48
Is this because of a permission? Or there are other reasons? – Alireza Jul 25 '14 at 12:59
Yes, it's a big database, used by several programs. So it's not that easy ;-) – Obl Tobl Jul 25 '14 at 13:03
1

Ok. I see. So it's hard but not totally impossible. I'm thinking of some another way. – Alireza Jul 25 '14 at 13:07
1

with much begging it's MAYBE possible. But nevertheless i would prefer other solutions. – Obl Tobl Jul 25 '14 at 13:09
The problem is `SET NOCOUNT`'s scope is the current batch. Even putting a SET on a previous trigger will not apply it to the next. I give up! – Alireza Jul 25 '14 at 13:21

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

I think @Alireza's answer makes the most sense if it's possible to change the triggers but if it's not could you change your database call to execute the update statement and return @@ROWCOUNT?

_context.Database.SqlQuery<int>("update xx ...; select @@ROWCOUNT");

I can't find any documentation on MSDN but this question confirms that @@ROWCOUNT is unaffected by triggers.

edited May 23 '17 at 10:33

Community

1
1

answered Jul 25 '14 at 13:13

petelids

12,305
3
47
57

This could be a better solution regarding this special case – Alireza Jul 25 '14 at 13:15
yes, in my case for sure. Otherwise the cleaner solution is yours @Alireza – Obl Tobl Jul 25 '14 at 13:18
1

And we are here to solve `your` problem, of course. No anyone else's :) – Alireza Jul 25 '14 at 13:19

Get only result of update query

2 Answers2