How to grab the T-sql statement that fired a trigger??

Question

How to grab the T-sql statement that fired a trigger??

Brian Huse

Old Hand

Points: 374
More actions
July 23, 2010 at 6:33 pm

#141477

I'm putting together a simple trigger that stores the DML query being ran against a database table. All I want to do is save the entire query to a varchar field in an audit table.
I've tried the sys.dm_exec_sql_text(@sql_handle) process, but it returns the trigger its running in, instead of the statement that caused the trigger to fire.
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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Brian I have this saved in my snippets as the solution for getting most of the command(DBCC inputbuffer is limited to 256 chars)

this worked at the end of a long thread I participated in on the same subject: this thread was trying to find out who/what process was setting a column value to null, which the OP figured to do in a trigger.

as far as the sysadmin thing, just have your trigger use EXECUTE AS to solve the sysadmin issue.

CREATE TABLE WHATEVER(

WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DESCRIP VARCHAR(30)

)

INSERT INTO WHATEVER(DESCRIP)

SELECT 'APPLES' UNION

SELECT 'ORANGES' UNION

SELECT 'BANANAS' UNION

SELECT 'GRAPES' UNION

SELECT 'CHERRIES' UNION

SELECT 'KIWI'

--used to capture the row id plus a bunch of audit information

CREATE TABLE [dbo].[WHATEVER_AUDIT] (

[WHATEVERID] INT NOT NULL,

[INSERTUPDATE] NVARCHAR(30) NULL,

[LASTCOMMAND] NVARCHAR(max) NULL,

[USER_NAME] NVARCHAR(256) NULL,

[SUSER_NAME] NVARCHAR(256) NULL,

[CURRENT_USER] NVARCHAR(256) NULL,

[SYSTEM_USER] NVARCHAR(256) NULL,

[SESSION_USER] NVARCHAR(256) NULL,

NVARCHAR(256) NULL,

[APPLICATION_NAME] NVARCHAR(256) NULL,

[HOST_NAME] NVARCHAR(256) NULL,

[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)

GO

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT,UPDATE

AS

BEGIN

DECLARE @INSERTUPDATE NVARCHAR(30),

@LASTCOMMAND NVARCHAR(max)

--################################################################################################

--note these two methods do not get the last command when inside a trigger;

--included for complete solution

--get the last command by the current spid:

--DECLARE @handle varbinary(64)

--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID

--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)

--get the last command by the current spid:

--SELECT @LASTCOMMAND = DEST.TEXT

--FROM sys.[dm_exec_connections] SDEC

--CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST

--WHERE SDEC.[most_recent_session_id] = @@SPID

--################################################################################################

--because dbcc inputbuffer is limited to 256 chars, you may need to combine this witha DML trace

--################################################################################################

DECLARE @SQLBuffer nvarchar(4000)

DECLARE @buffer TABLE (

EventType nvarchar(30),

Parameters int,

EventInfo nvarchar(4000)

)

INSERT @buffer

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

SELECT @LASTCOMMAND = EventInfo

FROM @buffer

--assume it is an insert

SET @INSERTUPDATE='INSERT'

--if there's data in deleted, it's an update

IF EXISTS(SELECT * FROM DELETED)

SET @INSERTUPDATE='UPDATE'

--insert data that meets the criteria: the column 'description' is null

INSERT INTO [WHATEVER_AUDIT]

SELECT

INSERTED.WHATEVERID,

@INSERTUPDATE,

@LASTCOMMAND,

user_name() AS [user_name],

suser_name() AS [suser_name],

current_user AS [current_user],

system_user AS [system_user],

session_user AS [session_user],

user AS ,

APP_NAME() AS [application_name],

HOST_NAME() AS [host_name],

getdate() AS [occurance_date]

FROM INSERTED

WHERE DESCRIP IS NULL

END --TRIGGER

GO

--does not trigger audit:

INSERT INTO WHATEVER(DESCRIP)

SELECT 'CANTALOUPE' UNION

SELECT 'TANGARINES' UNION

SELECT 'PLUMS' UNION

SELECT 'PEACHES' UNION

SELECT 'BLUEBERRIES'

--triggers one row out of multi row insert

INSERT INTO WHATEVER(DESCRIP)

SELECT NULL UNION

SELECT 'TANGARINES'

--triggers one row out of multi row insert

UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)

SELECT * FROM WHATEVER

SELECT * FROM [WHATEVER_AUDIT]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 2

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]

Brian Huse Old Hand Points: 374 More actions · Answer 3

Thanks everyone for the assist -- was able to do what I needed, I was just hoping to do it without the extra security concerns of Exec AS. Just had to spend more time locking down permissions around the account I used for the BCSS call.

Brian Huse Old Hand Points: 374 More actions · Answer 4

Here is some more fun on this topic:

dotNET when it generates TSQL tends to like and hide the actual data -- This is a query that I've captured via the trigger.

(@p0 decimal(4,0),@p1 varchar(8000),@p2 varchar(8000),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),@p6 varchar(8000),@p7 char(1),@p8 decimal(4,0),@p9 varchar(8000))INSERT INTO [dbo].[ClientProfile]([CltPCltId], [CltPObjName], [CltPObjVal], [CltPExtra1], [CltPExtra2], [CltPDescription], [CltPDynamicFlag], [CltPIsDir]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7) SELECT [t0].[rowguid] FROM [dbo].[ClientProfile] AS [t0] WHERE ([t0].[CltPCltId] = @p8) AND ([t0].[CltPObjName] = @p9)

Anyone have any idea of a good way to get an expanded version of this?

Or maybe a dynamic way of building a 'col name'='value' list from the inserted/deleted trigger tables. I'm using the same base trigger template accross multiple tables, so I'd rather not code each trigger to be table specific.