You are here:Home » tsql » How to get SQL Statement executed for tables with audit history through Trigger in SQL Server

How to get SQL Statement executed for tables with audit history through Trigger in SQL Server

-- Create t table to test trigger

CREATE TABLE tbl_TriggerTest
(
[id] SMALLINT,
[name] VARCHAR(50),
)


--Create audit history table to store result from trigger

CREATE TABLE tbl_AuditHistory
(
[id] INT IDENTITY(1,1),
[AuditDate] DATETIME DEFAULT(GETDATE()) ,
[TableName] VARCHAR(50),
[LoginName] VARCHAR(50),
[HostName] VARCHAR(50),
[SQLText] VARCHAR(max),
)

GO

-- Create a trigger

ALTER TRIGGER TR_TBLFired ON tbl_TriggerTest FOR INSERT, UPDATE, DELETE
AS BEGIN

CREATE TABLE #DBCC (EventType varchar(50), Parameters varchar(50), EventInfo nvarchar(max))

INSERT INTO #DBCC
EXEC ('DBCC INPUTBUFFER(@@SPID)')

INSERT INTO tbl_AuditHistory ([TableName], [LoginName],[HostName], [SQLText])
SELECT 'tbl_TriggerTest',
SUSER_NAME() ,
HOST_NAME() ,
EventInfo
FROM #DBCC
END
GO


-- Now test all the things by insrting, updating and deleting records


--Insert
INSERT INTO tbl_TriggerTest SELECT 1,'Inserted1'
INSERT INTO tbl_TriggerTest SELECT 2,'Inserted2'
INSERT INTO tbl_TriggerTest SELECT 3,'Inserted3'

--Update
UPDATE tbl_TriggerTest SET [name] = 'Updated2' WHERE [id] = 2
UPDATE tbl_TriggerTest SET [name] = 'Updated3' WHERE [id] = 3

--Delete
DELETE FROM tbl_TriggerTest WHERE [id] = 1


-- Let's see the result of Audit table

SELECT * FROM tbl_AuditHistory