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.

Thanks

share|improve this question

4 Answers 4

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

Here is the sample for DDL trigger that captures SQL query too:

CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO Audit_DDL_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

The results from the appropriate table:

CREATE TABLE Audit_DDL_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);

would look like:

enter image description here

share|improve this answer

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)) + ')'
CREATE TABLE #SQL (
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql

SELECT @sql = EventInfo FROM #SQL
DROP TABLE #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

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

ALTER TRIGGER Audit_Test 
ON Test 
FOR INSERT, UPDATE, DELETE 
AS 
BEGIN 
   DECLARE @TEMP TABLE 
   (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000)) 
   INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)') 
   SELECT EventInfo FROM @TEMP 
END 
GO 

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

Your Answer

 
discard

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.