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:
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?
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
answered Oct 21, 2009 at 04:46 AM
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.
answered Nov 18, 2009 at 03:41 PM