Sql-server – Capture the Query Text of a DML Change

dmlsql servert-sqltrigger

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML query text, similar to using the below logic when a DML transaction is run against a table:

IF ((@icount > 0) AND (@dcount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

IF ((@dcount > 0) AND (@icount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

When I use the above logic and try to insert the @querytext variable to a table, it is NULL or passes in the trigger text – meaning that its context is not the actual insert or update, so it may not be possible to capture the full text and have to do a column-by-column DML save.

Best Answer

Have you considered using a trace for that?
https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx
You would be able to configure it for specific tables and store output in a table.

I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table. In some situations you will also get the constant values e.g.

DECLARE @ID_to_delete INT
SET @ID_to_delete = 123
DELETE FROM SampleTable WHERE ID = @ID_to_delete

While in most other cases you will get just the query used to change data e.g.

DELETE FROM SampleTable WHERE ID IN( SELECT ID_to_delete FROM ToDeleteTable )
Related Question