Erin Ramsay (8/13/2012)
Hey Lowell,Is there a way to set that DBCC inputbuffer code on the database level so that when ANY trigger fires you can get the schema name, table name, trigger name and the DBCC inputbuffer that caused that trigger to fire?
Erin
well I'm not sure I'm reading your question right.... this code snippet, inside any DML trigger, would get what you are asking for, but i'm not 100% sure it was you asked;
that would end up being part of your trigger model to be in every trigger, i think.
--assuming inside a trigger:
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
SELECT
SCHEMA_NAME(objz.schema_id) AS SchemaName,
objz.name AS TableName,
OBJECT_NAME(@@PROCID) AS TriggerName,
bufz.EventInfo AS LastCommand
FROM sys.objects
WHERE OBJECT_ID IN(SELECT
parent_object_id
FROM sys.objects objz
WHERE OBJECT_ID = @@PROCID)
CROSS JOIN @buffer bufz
Lowell