May 11, 2011 at 9:40 am
I'm troubleshooting a data problem where we have text values from records getting crossed up. What I'd love to be able to do is capture the actual T-SQL command that causes the update trigger to fire. Does anyone know of a SQL Server function to find out the command that is being processed when a trigger fires--just like what you see when you click Details on a connection in the Activity Monitor where it shows you the last T-SQL Command.
Thanks in advance!
Kenneth.
May 11, 2011 at 9:42 am
I've used dbcc input buffer in the past to do that (sql 2000).
Maybe there's a dmv now for it but I don't know it on top of my head.
May 11, 2011 at 10:36 am
Thanks--that does the trick. Here's the trigger for posterity. It writes the length of a text field before and after the update into another table. Also grabs the command; but not the parameters passed to the command, unfortunately.
----------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_Applications_UPDATE]
ON [dbo].[T_Applications]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t_cmd table (EventType varchar(30), [Parameters] int, [EventInfo] varchar(max))
DECLARE @dbcc varchar(255)
SET @dbcc = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS varchar(5)) + ') WITH NO_INFOMSGS'
INSERT INTO @t_cmd EXEC (@dbcc)
INSERT INTO T_App_SD_Change (app_id, changed, lenBefore, lenAfter, command)
SELECT i.app_id, GETDATE()
, ISNULL((SELECT TOP 1 lenAfter FROM T_App_SD_Change WHERE app_id = i.app_id ORDER BY sdcid DESC),0)
, ISNULL(DATALENGTH(a.score_data),0)
, cmd.EventInfo + ' [' + CAST(cmd.[Parameters] AS varchar(255)) + ']'
FROM inserted i
INNER JOIN T_Applications a (NOLOCK) ON i.APP_ID = a.APP_ID
CROSS JOIN @t_cmd cmd
END
------------------------------------------------
May 11, 2011 at 10:40 am
Ya I know of those limitations. That's why I was hoping someone had something already tested.
The only option I have in mind right now is sp_WhoIsActive. You should be able to tweak that out and get exactly what you need...
May 12, 2011 at 4:14 am
How about profiler?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 12, 2011 at 5:21 am
It works but you have to wade through a lot of data to get very little data.
With the trigger you're able to just log the bare minimum.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply