I am creating a Tracker table where I can store my DML operation execution Query for INSERT, UPDATE, and DELETE operation.
Creating trigger with name of Query_tracker_Trig
select * from Query_Tracker
Create table Query_Tracker ( ID int unique identity(1,1), Host varchar(15), [Date] datetime, Context varchar(max) )
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
No comments:
Post a Comment