HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fromresulttriggerthereliablefiredwaybeingactiondml

Problem

Is there any way to tell from within a trigger, when it gets fired, that it is being fired as the result of a DML action that occurred within another specific trigger?

Any chance any information about the call stack is exposed in the EVENTDATA() function? Or another function? I'd prefer not having to shred XML.

I'm ideally shooting for getting the name of the original trigger that executed the DML that caused the second trigger to fire, from within the scope of that second trigger. But I'm open to similar ways of identifying the source too.

I have full control over the code of both triggers in question.

Solution

You can use TRIGGER_NESTLEVEL with an objectid parameter

IF TRIGGER_NESTLEVEL(@@PROCID) > 0
BEGIN
    -- do stuff
END;


The EVENTDATA() function is only relevant for DDL triggers anyway.

Code Snippets

IF TRIGGER_NESTLEVEL(@@PROCID) > 0
BEGIN
    -- do stuff
END;

Context

StackExchange Database Administrators Q#327833, answer score: 15

Revisions (0)

No revisions yet.