Below is an example of how to use a Trigger to audit who updated, deleted or inserted data. The trigger also captures the old and new values when an update is performed:
CREATE TABLE dbo.tblProducts
(
ProductId INT IDENTITY(1, 1) CONSTRAINT PK_tblProducts PRIMARY KEY,
ProductName VARCHAR(20)
)
GO
CREATE TABLE dbo.tblProductsHISTORY
(
ProductId INT,
OldProductName VARCHAR(20),
NewProductName VARCHAR(20),
DateUpdated DATETIME,
UpdatedBy NVARCHAR(256),
Statement CHAR(1)
)
GO
CREATE TRIGGER dbo.tr_tblProducts ON dbo.tblProducts
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
— update
INSERT INTO tblProductsHISTORY
SELECT t1.productid, t1.productname, t2.productname, GETDATE(), SYSTEM_USER, ‘U’
FROM deleted t1 JOIN inserted t2
ON t1.productid = t2.productid
JOIN tblProducts t3
ON t1.productid = t3.productid
END
ELSE
BEGIN
— insert
INSERT INTO tblProductsHISTORY
SELECT productid, productname, productname, GETDATE(), SYSTEM_USER, ‘I’
FROM inserted
END
END
ELSE
BEGIN
— delete
INSERT INTO tblProductsHISTORY
SELECT productid, productname, productname, GETDATE(), SYSTEM_USER, ‘D’
FROM deleted
END
