x

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

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

more ▼

asked Oct 20, 2009 at 11:46 AM in Default

sbendayan gravatar image

sbendayan
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Oct 21, 2009 at 04:46 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 18, 2009 at 03:41 PM

AjarnMark gravatar image

AjarnMark
100 3 3 5

(comments are locked)
10|1200 characters needed characters left

i think what you need to use is @@ProcID

more ▼

answered Nov 04, 2009 at 12:19 PM

MladenPrajdic gravatar image

MladenPrajdic ♦
282 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x121

asked: Oct 20, 2009 at 11:46 AM

Seen: 2009 times

Last Updated: Oct 20, 2009 at 12:33 PM