Friday 2 June 2017

Track Your Execution Query through Trigger

I am creating a Tracker table where I can store my DML operation execution Query for INSERT, UPDATE, and DELETE operation.
Create table Query_Tracker
(
ID int unique identity(1,1),
Host varchar(15),
[Date] datetime,
Context varchar(max)
)
Creating trigger with name of Query_tracker_Trig
Create trigger [dbo].[Query_tracker_Trig]
ON [dbo].[Items_Copy]
after UPDATE,INSERT,DELETE
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS nvarchar(100)) + ')'
CREATE TABLE #SQL
(
    EventType varchar(100),
    Parameters int,
    EventInfo nvarchar(max)
)
INSERT INTO #SQL
EXEC sp_executesql @sql
SELECT @sql = EventInfo FROM #SQL
INSERT INTO Query_Tracker(Host,[Date],Context)VALUES(Host_name(),getDate(),@sql)
DROP TABLE #SQL
END
select * from Query_Tracker

No comments:

Post a Comment