Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it?

I need to know the SQL query that fires a trigger over a database without a profiler.


share|improve this question
add comment

3 Answers

The following seems to do the trick to retrieve the current batch/statement:

SELECT current_batch     = dest.text
     , current_statement = SUBSTRING(dest.text, dem.statement_start_offset/2, CASE WHEN dem.statement_end_offset=-1 THEN 8000 ELSE (dem.statement_end_offset-dem.statement_start_offset)/2 END)
FROM   sys.dm_exec_requests dem CROSS APPLY sys.dm_exec_sql_text(dem.sql_handle) dest
WHERE  session_id = @@SPID

though IIRC if the call is to a stored procedure you may get the code for the procedure returned rather than the text of the call depending on how the request was submitted (ad-hoc SQL, prepared procedure call, etc...). I'm not sure if in a trigger you'll end up getting the code for the trigger instead of what you are looking for, so you'll need to test that.

To see the difference between batch and statement, run something like:

DECLARE @current_batch NVARCHAR(MAX), @current_statement NVARCHAR(MAX)
-- start of statement that will be returned by itself
SELECT @current_batch     = dest.text
     , @current_statement = SUBSTRING(dest.text, dem.statement_start_offset/2, CASE WHEN dem.statement_end_offset=-1 THEN 8000 ELSE (dem.statement_end_offset-dem.statement_start_offset)/2 END)
FROM   sys.dm_exec_requests dem CROSS APPLY sys.dm_exec_sql_text(dem.sql_handle) dest
WHERE  session_id = @@SPID
-- end of statement that will be returned by itself
-- results:
PRINT '---- BATCH --------------------------------------------'
PRINT @current_batch
PRINT '---- STATEMENT ----------------------------------------'
PRINT @current_statement

via SQL Server Management Studio or similar.

share|improve this answer
Inside a trigger I doesn't work because I get the code of the trigger as a caller. Thanks for the help –  Plebios Jul 17 '13 at 13:28
sys.dm_exec_requests "returns information about each request that is executing within SQL Server" link. This view will not give you the history of all requests. You may see only the active/executing requests. Life of these requests are [usually] short. You have to be very fast (when you press F5 in SSMS) to catch all requests. –  Bogdan Sahlean Jul 17 '13 at 15:34
BogdanSahlean: Aye, though we only want to see the current activity here not historic: the point of the question I think was to log that somewhere for later reporting as well as taking the triggers normal action. @Plebios: have a scan of the documentation around the sys.* views/functions: it may be possible to work your way back to the original statement rather than the current trigger's code, though I've never needed to do this myself confirm if it is/isn't doable. If not, then you'll be stuck adding code to your DA or BL layers to provide the audit records you are looking for. –  David Spillett Jul 17 '13 at 16:16
David Spillett: Finally I get what I want using DBCC INPUTBUFFER –  Plebios Jul 17 '13 at 16:39
Be careful to performance test anything based on this discussion: there could be lock waiting issues and similar and you need to be very careful of the performance (and locking footprint) of anything you ask a trigger to perform. –  David Spillett Jul 17 '13 at 16:48
show 1 more comment

I have a number of triggers that do this, and I find that DBCC INPUTBUFFER is generally the best way to do it. Caution: the output is limited to 4000 characters. Very long queries will be truncated.

DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
EXEC sp_executesql @sql

SELECT @sql = EventInfo FROM #SQL

At the end of this, @sql contains the query for the current request. Also, you could just as easily use a table variable instead of a temp table.

share|improve this answer
add comment

Searching over the web I found the solution to my answer. The key is use DBCC INPUTBUFFER:

ON Test 
   (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000)) 

It works for me!

share|improve this answer
Hmmm: INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)') ? Why you insert into a table variable (@TEMP) ? –  Bogdan Sahlean Jul 17 '13 at 16:47
The table is created to store the information that is get. In my case my trigger just Insert the data over a physical table –  Plebios Jul 17 '13 at 16:59
"The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this." link –  Bogdan Sahlean Jul 17 '13 at 17:01
For INSERT and UPDATE statements: the trigger will not be executed if the constraints (ex. CHECK) are not fulfilled. –  Bogdan Sahlean Jul 17 '13 at 17:07
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.