Prev: Could not find stored procedure - fails on first attempt only
Next: PIVOT with dynamic where clause (how to)
From: VSLA on 15 Mar 2010 20:11
Thank you, removing the nest level makes it work perfectly.
Thanks!

"Erland Sommarskog" wrote:

> VSLA (VSLA(a)discussions.microsoft.com) writes:
> > Create Trigger tu_Contacts
> > On search.dbo.Contacts
> > For Update
> > as
> > Begin
> > If @@Nestlevel<=1
> > Update search.dbo.Contacts
> > Set [timestamp]=getdate()
> > From search.dbo.Contacts c
> > Inner Join inserted i
> > on c.[ContactId] = i.[ContactId]
> > End
>
>
> The condition on @@nestlevel is the culprit. It will work if you run a
> direct UPDATE from the top-level scope. But if you perform the update
> from within a stored procedure or another trigger it will not. When
> you run from Access, it is very likely that you generate a parameterised
> statement (good boy!), which is executed with sp_executesql and whence
> @@nestlevel will be > 1.
>
> I'm not really sure what you want to achieve, but you should probably remove
> the check @@nestlevel entirely.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Could not find stored procedure - fails on first attempt only
Next: PIVOT with dynamic where clause (how to)