Sql-server – SQL Server audit query that fires a trigger

sql serversql-server-2008-r2trigger

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

Best 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.

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

Sql-server – SQL Server: update trigger fires before insert trigger

Considering that a) Triggers are naturally part of the Transaction that is the DML statement that fired the Trigger, and b) an UPDATE cannot happen on a row until the row exists, it is impossible for the actual UPDATE to show up before the actual INSERT. Hence, something else is going on.

Things to consider:

Are the Trigger definitions shown in the question the actual and current definitions? Is it possible that the Comment values of "Insert" and "Update" are switched in the Triggers such that the INSERT Trigger has the comment of "Update" and vice-versa?
Is it possible that the UPDATE Trigger is somehow defined as being AFTER INSERT, UPDATE ? If so, an INSERT operation would fire both triggers making it look like both an INSERT and an UPDATE happened when in fact there was no UPDATE operation (which might also explain why the times are only a few milliseconds apart for the audit entries).
Is it possible that your query to determine "corresponding" records is flawed and that the results are misleading?

Best Answer

Related Solutions

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

Sql-server – SQL Server: update trigger fires before insert trigger

Related Question