How to get the SQL code of the DML that fires a trigger?

Question

Greetings,

I'm using SQL Server 2005 and I have a need to get the SQL code that fires a trigger but I haven't been able to do this programmatically. I found a query on the DMVs that will get me the statement I need, but it doesn't work when run inside a trigger, which is where I need it to run. Here is the query:

SELECT text FROM MASTER.sys.dm_exec_connections CROSS APPLY MASTER.sys.dm_exec_sql_text(most_recent_sql_handle) WHERE session_id = @@SPID

When I run this query inside the trigger, all I get is the CREATE TRIGGER statement, which is totally useless. When I run the same query outside the trigger I get the DML statement (an INSERT, in this case) that fires the trigger.

Is there any way to make this work in a trigger? If not, what other options do I have? Is there anything undocumented that I can use to get this information? It must be in the Transaction Log somewhere, so maybe mining the output of the DBCC LOG command is an option?

Thanks,

SB

Answer 1

0

Note that the code you used will show you anything that is run lastly. It can be single statement or group of statements. Why dont you just print/select the actual code used at the end of the trigger

ex

create trigger............            
as            
your code here            
            
select 'your code here' as sql

answered Oct 21 '09 at 04:46 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

(comments are locked)

Answer 2

Triggers are fired from events (loose jargon) such as an INSERT, UPDATE, or DELETE. There is a variety of actual statements that could be issued to cause such a thing, including the firing of another trigger.

What is the end goal of what you are trying to do? There is probably a better way other than capturing the calling statement. For example, in triggers you can test for which columns were updated using the COLUMNS_UPDATED() function. You can also glean a lot of information from the special trigger-only tables named inserted and deleted. If you are building your own auditing features, typically the information from inserted and deleted along with some information such as CURRENT_TIMESTAMP and ORIGINAL_LOGIN or SUSER_SNAME() is sufficient to track who made what changes without having to know the exact syntax of the request.

Answer 3

0

i think what you need to use is @@ProcID

answered Nov 04 '09 at 12:19 PM

MladenPrajdic ♦
282 ● 2 ● 2 ● 3

(comments are locked)

How to get the SQL code of the DML that fires a trigger?

Follow this question

Related Questions