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 15:53
I want to create a simple trigger to put in a timestamp when the record was
last modified.
This works when I am updating a record in SQL Server, but if I am using an
external source, such as MS Access, the trigger does not update the record
with the modify date.
The trigger should run, irregardless of where the data is being updated.
Thoughts?
From: Tom on 15 Mar 2010 16:18
On Mar 15, 3:53 pm, VSLA <V...(a)discussions.microsoft.com> wrote:
> I want to create a simple trigger to put in a timestamp when the record was
> last modified.
> This works when I am updating a record in SQL Server, but if I am using an
> external source, such as MS Access, the trigger does not update the record
> with the modify date.
> The trigger should run, irregardless of where the data is being updated.
> Thought

Show us the code that created the trigger. If you created an update
trigger on the SQL Server table it will work whenever the table is
updated without respect to where the update originated.
From: VSLA on 15 Mar 2010 17:05
Thanks, Tom.
here it is:

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

Go



"Tom" wrote:

> On Mar 15, 3:53 pm, VSLA <V...(a)discussions.microsoft.com> wrote:
> > I want to create a simple trigger to put in a timestamp when the record was
> > last modified.
> > This works when I am updating a record in SQL Server, but if I am using an
> > external source, such as MS Access, the trigger does not update the record
> > with the modify date.
> > The trigger should run, irregardless of where the data is being updated.
> > Thought
>
> Show us the code that created the trigger. If you created an update
> trigger on the SQL Server table it will work whenever the table is
> updated without respect to where the update originated.
> .
>
From: Tom on 15 Mar 2010 18:39
On Mar 15, 5:05 pm, VSLA <V...(a)discussions.microsoft.com> wrote:
> Thanks, Tom.
> here it is:
>
> 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
>
> Go
>
>
>
> "Tom" wrote:
> > On Mar 15, 3:53 pm, VSLA <V...(a)discussions.microsoft.com> wrote:
> > > I want to create a simple trigger to put in a timestamp when the record was
> > > last modified.
> > > This works when I am updating a record in SQL Server, but if I am using an
> > > external source, such as MS Access, the trigger does not update the record
> > > with the modify date.
> > > The trigger should run, irregardless of where the data is being updated.
> > > Thought
>
> > Show us the code that created the trigger. If you created an update
> > trigger on the SQL Server table it will work whenever the table is
> > updated without respect to where the update originated.
> > .- Hide quoted text -
>
> - Show quoted text -

Why are you checking the value of @@NESTLEVEL. The definition of this
is from BOL

Each time a stored procedure calls another stored procedure or
executes managed code by referencing a common language runtime (CLR)
routine, type, or aggregate, the nesting level is incremented. When
the maximum of 32 is exceeded, the transaction is terminated.

If the procedure that called this is nested more than 1 it will not
execute.

I suspect what you really want to check is

IF TRIGGER_NESTLEVEL(object_ID('dbo.tu_Contacts')) > 1 RETURN;

You want to check the level of trigger nesting. This is often required
for triggers on table involved in replication. The definition of
TRIGER_NESTLEVEL is from BOL
Returns the number of triggers executed for the statement that fired
the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to
determine the current level of nesting.

From: Erland Sommarskog on 15 Mar 2010 18:49
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

 |  Next  |  Last
Pages: 1 2
Prev: Could not find stored procedure - fails on first attempt only
Next: PIVOT with dynamic where clause (how to)