I have a table (i.e. SourceTable) that contains an AFTER INSERT, UPDATE, DELETE trigger. When the trigger fires it copies the inserted row from INSERT & UPDATE triggers or the deleted row from DELETE trigger in AuditTable. I need a query to list what field was changed and the old & new values. Below is an example of the SourceTable and it's triggers, plus some sample audit data with expected result data. How do you suggest I get the result data?
DROP TABLE IF EXISTS SourceTable;
CREATE TABLE SourceTable (Id int, Color varchar(50), ShipDate date);
GO
CREATE TRIGGER trIUD_Audit
ON SourceTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
--AFTER INSERT trigger on source table copies the inserted row into AuditTable with AuditType = 'I'
--AFTER UPDATE trigger on source table copies the inserted row into AuditTable with AuditType = 'U'
--AFTER DELETE trigger on source table copies the deleted row into AuditTable with AuditType = 'D'
DECLARE @action as char(1) = 'I'; -- set Action to Insert by default.
IF EXISTS(SELECT * FROM deleted)
SET @action = CASE WHEN EXISTS(SELECT * FROM inserted) THEN 'U' ELSE 'D' END;
IF @action = 'I'
BEGIN
INSERT INTO AuditTable (
Id,
Color,
ShipDate,
AuditAction,
AuditDatetime)
SELECT
Id,
Color,
ShipDate,
@action,
GETDATE()
FROM inserted;
END;
ELSE IF @action = 'U'
BEGIN
INSERT INTO AuditTable (
Id,
Color,
ShipDate,
AuditAction,
AuditDatetime)
SELECT
Id,
Color,
ShipDate,
@action,
GETDATE()
FROM
(
-- get only records that had a field changed in UPDATE because UPDATE will fire this trigger even if field values don't change
SELECT *
FROM inserted
EXCEPT
SELECT *
FROM deleted
) AS U;
END;
ELSE IF @action = 'D'
BEGIN
INSERT INTO AuditTable (
Id,
Color,
ShipDate,
AuditAction,
AuditDatetime)
SELECT
Id,
Color,
ShipDate,
@action,
GETDATE()
FROM deleted;
END;
RETURN;
END;
GO
DROP TABLE IF EXISTS AuditTable;
CREATE TABLE AuditTable (Id int, Color varchar(50), ShipDate date, AuditId int, AuditAction char(1) CHECK (AuditAction IN ('I','U','D')), AuditDatetime datetime);
GO
DECLARE @d datetime = GETDATE();
INSERT INTO AuditTable VALUES
(1, 'Blue', NULL, 1, 'I', GETDATE()),
(1, 'Red', @d, 2, 'U', DATEADD(minute, 1, @d)),
(1, 'Red', @d, 3,'D', DATEADD(minute, 2, @d)),
(2, 'Green', @d, 4,'U', DATEADD(minute, 4, @d)),
(2, 'Green', NULL, 5,'U', DATEADD(minute, 5, @d)),
(2, 'Green', NULL, 6,'D', DATEADD(minute, 6, @d)),
(2, 'Blue', NULL, 7,'I', DATEADD(minute, 7, @d));
SELECT *
FROM AuditTable
ORDER BY Id, AuditDatetime;
DROP TABLE IF EXISTS OutputData;
CREATE TABLE OutputData (Id int, Field varchar(50), OldValue varchar(MAX), NewValue varchar(MAX), AuditId int, AuditAction char(1), AuditDatetime datetime);
INSERT INTO OutputData VALUES
(1, 'Color', NULL, 'Blue', 1, 'I', @d),
(1, 'Color', 'Blue', 'Red', 2, 'U', DATEADD(minute, 1, @d)),
(1, 'ShipDate', NULL, CAST(CAST(@d AS date) AS varchar(MAX)), 2, 'U', DATEADD(minute, 1, @d)),
(1, 'Color', 'Red', NULL, 3, 'D', DATEADD(minute, 2, @d)),
(1, 'ShipDate', CAST(CAST(@d AS date) AS varchar(MAX)), NULL, 3, 'D', DATEADD(minute, 2, @d)),
(2, 'ShipDate', CAST(CAST(@d AS date) AS varchar(MAX)), NULL, 5, 'U', DATEADD(minute, 5, @d)),
(2, 'Color', 'Green', NULL, 6, 'D', DATEADD(minute, 6, @d)),
(2, 'Color', NULL, 'Blue', 7, 'I', DATEADD(minute, 7, @d));
--Notice Id 2 doesn't have an INSERT, so we can't compare update AuditId 5 to an INSERT
-- results
SELECT *
FROM OutputData
ORDER BY AuditDatetime;
GO
--cleanup
DROP TRIGGER trIUD_Audit;
DROP TABLE AuditTable;
DROP TABLE OutputData;
GO
