Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to grab the T-sql statement that fired a trigger?? Expand / Collapse
Author
Message
Posted Friday, July 23, 2010 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 5:34 PM
Points: 4, Visits: 15
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?


Post #958337
Posted Friday, July 23, 2010 9:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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,
[USER] 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 [user],
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #958345
Posted Saturday, July 24, 2010 8:37 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:38 PM
Points: 382, Visits: 1,156
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.


Sankar Reddy | http://SankarReddy.com/
Post #958451
Posted Tuesday, July 27, 2010 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 5:34 PM
Points: 4, Visits: 15
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.
Post #959569
Posted Tuesday, July 27, 2010 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 5:34 PM
Points: 4, Visits: 15
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.


Post #959579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse