November 3, 2009 at 4:12 pm
Hi smart people!
We have an issue in production that we are having a very hard time tracking down. We need to find out what stored procedures inserted data into our table, but this data needs to be available to a trigger that is fired on an update. Is there any way that our trigger can trace the inserting proc, or get data from the call stack?
November 3, 2009 at 5:45 pm
Not sure if this is exactly what you're looking for but it should get you started down the right path. If you create the below table and put the trigger on the table you're interested in it should give you the SQL that was run to insert into the table.
CREATE TABLE [dbo].[InputBufferHistory](
[inputBufferHistoryID] [int] IDENTITY(1,1) NOT NULL,
[EventType] [nvarchar](30) NULL,
[Parameters] [int] NULL,
[EventInfo] [nvarchar](4000) NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK_InputBufferHistory] PRIMARY KEY CLUSTERED
(
[inputBufferHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InputBufferHistory] ADD CONSTRAINT [DF_InputBufferHistory_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
CREATE TRIGGER [dbo].[InsertTrigger_tr]
ON [dbo].[EventHistory]
AFTER INSERT
AS
INSERT INTO dbo.InputBufferHistory
(EventType,
Parameters,
EventInfo)
EXEC ('DBCC INPUTBUFFER (' + @@SPID + ')')
November 3, 2009 at 8:19 pm
Very nice! Thanks!
November 4, 2009 at 8:30 am
This worked like a CHAMP! I added the insert to our existing trigger, and it caught my insert details in the failure condition. Now if this thing recreates itself, we will know exactly what caused it.
I'll let you know if we catch our bug. Thanks again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply