Re: nocount

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Uri Dimant (urid_at_iscar.co.il)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 13:35:42 +0200

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...
> 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)

Relevant Pages

  • Re: Error on Trigger Launch
    ... I needed to move the "SET NOCOUNT ON" below the as ... trigger, ... DECLARE @tableHTML NVARCHAR; ... Fetch Next FROM email_curs into @email ...
    (comp.databases.ms-sqlserver)
  • Re: Why data could not be committed into table?
    ... deleted in the update trigger. ... DECLARE @PrevClose decimal ... SET NOCOUNT OFF ... > If the app uses sa you should be fine with access. ...
    (microsoft.public.sqlserver.programming)
  • Re: KB275090 Alternative workaround
    ... DECLARE @Sql VARCHAR ... SET NOCOUNT OFF ... IDENTITY values generated in a trigger. ... SCOPE_IDENTITYis available in SQL Server 2000 and later versions. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: adding record to sql db
    ... > create trigger tD_BCustomerCA on BCustomerCA for DELETE as ... > declare @numrows int, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: how to assign the contents of a field to a variable
    ... what happens if the MSMQ server is ... teh requirements of the trigger are to export an XML document to MSMQ ... DECLARE @int_msmqqueue INT ... IF @int_result 0 GOTO ErrorHandler ...
    (microsoft.public.sqlserver.programming)