what was the process that fired a delete trigger?

Question

what was the process that fired a delete trigger?

DarinH 21

I have a delete trigger that i need to know if the delete command was done from a stored procedure or a TSQL command. Is there a way to do that? I have tried CONTEXT_INFO() but that seems to always be returning NULL.

I am trying to determine what caused the delete of the row. So if it came from a TSQL then i want it to do a block of code, but if it came from a very specific stored procedure i do NOT want it to do that extra block of code.

Thanks.

DarinH 21 Reputation points

2022-02-03T13:11:38.523+00:00

Further information on my question:

I am trying to determine what caused the delete of the row. So if it came from a TSQL then i want it to do a block of code, but if it came from a very specific stored procedure i do NOT want it to do that extra block of code.

Answer accepted by question author

2 additional answers

Your answer

DarinH 21 Reputation points

2022-02-03T13:11:38.523+00:00

Further information on my question:

I am trying to determine what caused the delete of the row. So if it came from a TSQL then i want it to do a block of code, but if it came from a very specific stored procedure i do NOT want it to do that extra block of code.

Answer 1

I am trying to determine what caused the delete of the row. So if it came from a TSQL then i want it to do a block of code, but if it came from a very specific stored procedure i do NOT want it to do that extra block of code.

If you are on SQL 2016 or later, you can do:

EXEC sp_set_session_context N'SkipThisSection', 1
-- Command that fires trigger.
EXEC sp_set_session_context N'SkipThisSection', NULL

In the trigger you do:

IF isnull(convert(bit, SESSION_CONTEXT(N'SkipThisSection')), 0) = 0
BEGIN
    --  The special code goes here.
END

The second call to sp_set_session_context is needed to clear the "variable", else it will remain in the session.

On any version of SQL Server you can use a temp table as a flag. In the procedure you do:

CREATE TABLE #SkipThisSection (a int NOT NULL)
-- Command that fires trigger.

and in the trigger:

IF object_id('tempdb..#SkipThisSection') IS NULL
BEGIN
   -- The special code goes here.
END

`

Answer 2

Erland Sommarskog 133.7K MVP Volunteer Moderator

There is nothing built in to do this, but you need to cater for this yourself. You can indeed use SET CONTEXT_INFO and CONTEXT_INFO() for that purpose, but if you are on SQL 2016 it is better to use sp_sesssion_context and SESSION_CONTEXT(). That is your code needs to set the context and the trigger can read that.

You could look at @@nestlevel. If it's 2, you are obviously not being called from a stored procedure, but it has to be an ad-hoc batch. But if the T-SQL session is engaging in dynamic SQL @@nestlevel will be 3 or 4. Likewise if the trigger was fired by another trigger.

What is your original problem?

DarinH 21 Reputation points

2022-02-03T13:04:08.027+00:00

I am trying to determine what caused the delete of the row. So if it came from a TSQL then i want it to do a block of code, but if it came from a very specific stored procedure i do NOT want it to do that extra block of code.
Tom Phillips 17,786 Reputation points

2022-02-03T16:52:53.81+00:00

There is no built-in way for the trigger to know where the DELETE came from. That information does not exist.

You could set CONTEXT_INFO in your proc, and if the trigger finds something in CONTEXT_INFO it does not do that section of code. But you should not be doing conditional things in your trigger. That is very bad practice. Without knowing exactly what you are trying to do it is impossible to give you direction on another way.
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2022-02-03T22:12:32.7+00:00
Hey, I plead guilty of having done exactly what Darren is asking for!

In a system I used to work with, there are lots of triggers which includes code like:

IF object_id('tempdb..#update$script') IS NULL BEGIN -- Do stuff here END

When change table schemas in a way that the table has to be reloaded, we want business-rules in triggers to fire, but for obvious reasons, not cascading updates. So that latter are protected by the above check - our load tool creates this temp table.

But I have occasionally done exactly what Darrin is asking for. It could be a stored procedure that needs to temporarily violate a business rule inside a transaction. Unfortunately, SQL Server does not support commit-time triggers.
DarinH 21 Reputation points

2022-02-04T13:13:46.86+00:00

Is the "#update$script" the name of the stored procedure that is currently running and presumably made this trigger fire?
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2022-02-04T13:16:38.903+00:00

No, it's a temp table. As I said, our load tool creates this temp table creates this temp table.

I did compose a longer answer about this yesterday, but it appears that I failed to press Submit, because it is not visible. I will make a new attempt.

Answer 3

Hi @DarinH ,

SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when diagnosing a problem.

You may use SQL Server Profiler to monitor the delete command.

sql-server-profiler

Or you may use SQL Server Extended Events to monitor query performance.

using-sql-server-extended-events-to-monitor-query-performance

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

DarinH 21 Reputation points

2022-02-03T13:04:38.033+00:00

That won't work for me because i am trying to determine what caused the deletion within the trigger itself to determine if something else needs to be run or not.

Share via

what was the process that fired a delete trigger?

2 additional answers

Your answer