SQL Server audit query that fires a trigger

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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:

Answer 4

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!