nocount

Hi,
Is it true that setting nocount on in a trigger is almost
a good practice? When it is not true?
Many thanks,
Oskar
Oscar
When SET NOCOUNT ON SQL Server does not inform users about rows were
affected.
It is good practice to set it up at the beginning of stored procedures but i
have not seen this command within triggers. It's meaningless.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
> Hi,
> Is it true that setting nocount on in a trigger is almost
> a good practice? When it is not true?
> --
> Many thanks,
> Oskar
>
|||On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:

>Oscar
>When SET NOCOUNT ON SQL Server does not inform users about rows were
>affected.
>It is good practice to set it up at the beginning of stored procedures but i
>have not seen this command within triggers. It's meaningless.
Hi Uri,
This is not correct. Run the following code with and wothout the set
nocount on statement in the trigger to see the difference.
set nocount off
go
create table test (a int not null)
go
create table test2 (now datetime not null primary key default(getdate()),
num int not null)
go
create trigger testtrig on test after insert
as
--set nocount on
declare @.num int
select @.num = count(*)
from inserted
insert into test2(num)
select @.num
update test set a = a
go
insert test
select 1 union all select 2
go
select * from test2
go
drop table test2, test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Uri,
But what about this remark in books on-line:
"It is recommended that a trigger not return any results.
This is because special handling for these returned
results must be written into every application in which
modifications to the trigger table are allowed. To prevent
any results from being returned from a trigger, do not
include either SELECT statements or variable assignments
in the definition of the trigger. If variable assignment
must occur in a trigger, use a SET NOCOUNT statement at
the beginning of the trigger to eliminate the return of
any result sets."
I do assignments and selects in my triggers. I wonder if I
set nocount on in every trigger, will it have any
noticeable, positive effect.
Thanks,
Oskar

>--Original Message--
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users
about rows were
>affected.
>It is good practice to set it up at the beginning of
stored procedures but i
>have not seen this command within triggers. It's
meaningless.
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
almost
>
>.
>
|||Hi,Hugo
> declare @.num int
> select @.num = count(*)
What was a purpose to declare this variable and not using it?
If if does affect the perfomance (some big logic inside the trigger) so I
would not use trigger at all.
But now I have just finished read this article in the BOL and I will have
to review my triggers again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2jb2m0dpt5k6g9vjr8c0hkmftaf2amhumd@.4ax.com... [vbcol=seagreen]
> On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
but i
> Hi Uri,
> This is not correct. Run the following code with and wothout the set
> nocount on statement in the trigger to see the difference.
> set nocount off
> go
> create table test (a int not null)
> go
> create table test2 (now datetime not null primary key default(getdate()),
> num int not null)
> go
> create trigger testtrig on test after insert
> as
> --set nocount on
> declare @.num int
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num
> update test set a = a
> go
> insert test
> select 1 union all select 2
> go
> select * from test2
> go
> drop table test2, test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:

>Hi,Hugo
>What was a purpose to declare this variable and not using it?
Hi Uri,
It is used in the insert-statement following the select where it is set.
Of course, this trigger could have been written otherwise. The update
statement doesn't do anything usseful either. But I wanted a quick example
to show something and I didn't want to spend the time to make up something
more realistic.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi, Hugo
select @.num = count(*)
from inserted
insert into test2(num)
I see now , you just missed a @.,
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:b7i2m0pgmvg03aus9duhbdvf65df3mlejm@.4ax.com...
> On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
>
> Hi Uri,
> It is used in the insert-statement following the select where it is set.
> Of course, this trigger could have been written otherwise. The update
> statement doesn't do anything usseful either. But I wanted a quick example
> to show something and I didn't want to spend the time to make up something
> more realistic.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:

>Hi, Hugo
>select @.num = count(*)
>from inserted
>insert into test2(num)
>I see now , you just missed a @.,
Hi Uri,
Did I? Or did you miss a whole line?
select @.num = count(*)
from inserted
insert into test2(num)
select @.num -- Here, it is used!!!!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi ,Hugo
You said
>It is used in the insert-statement following the >select where it is set.
Now you are saying
> select @.num -- Here, it is used!!!!
Or it was just time to go sleep
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rmi3m0tsge4jufmj7cukaqv72r6km8kjq0@.4ax.com...
> On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
>
> Hi Uri,
> Did I? Or did you miss a whole line?
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num -- Here, it is used!!!!
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)