Posted by Razvan Socol on 08/08/06 05:14

Hi, Ted

It is a best practice to issue "SET NOCOUNT ON" at the beginning of
triggers (and stored procedures) that perform any INSERT/UPDATE/DELETE
statements, in order to eliminate the additional "n row(s) affected"
message which would show up as the effect of those statements. If the
statement that causes the trigger is executed using ADO or OLE-DB, the
additional messages may mask a real error message, which would not be
raised by ADO as an error, until the corresponding resultset would be
retrieved using the .NextRecordset method.

See the chaper on "Client-side Error Handling" in ADO and ADO.Net from
the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html

Here are some KB articles documenting similar problems:
http://support.microsoft.com/kb/q197528/
http://support.microsoft.com/default.aspx?scid=kb;en-us;195491
http://support.microsoft.com/kb/q240882/

Razvan

Ted wrote:
> Hello Razvan Socol
>
> Thanks for this. My error in using inserted is obvious after you
> pointed it out.
>
> What is the purpose of your "SET NOCOUNT ON"? Where is NOCOUNT defined
> or declared?
>
> Thanks again.
>
> Ted

• Next in forum: How to restrict evil create scripts?
• Prev in forum: What is the correct sequence of Restoring databases in SQL Server 7.0
• Thread view: Re: understanding triggers

[Reply to this message]