Home Forums SQL Server 2005 Administering How to grab the T-sql statement that fired a trigger?? RE: How to grab the T-sql statement that fired a trigger??

  • July 24, 2010 at 8:37 pm

    #1197910

    Brian Huse (7/23/2010)

    I've also tried the following

    SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

    INSERT INTO #inputbuffer

    EXEC (@ExecStr)

    SET @Qry = (SELECT EventInfo FROM #inputbuffer)

    which works, but requires everyone to have sysadmin privileges to execute.

    Anyone know a way to grab the T-SQL statement that caused the trigger to fire?

    You have to use EXECUTE AS LOGIN/USER to overcome the sysdamin privileges. The first time I knew about this is from Plamen Ratchev's blog post on this topic way back in 2008.

    http://pratchev.blogspot.com/2008/04/auditing-data-modification-statements.html

    Lowell gave you good information already before.

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]