I am working on SQL Server 2019
I am trying to create a trigger to audit changes on TestTable. Here's my trigger:
Create TRIGGER trg_insert_TestTable on TestTable
AFTER INSERT
AS
BEGIN
INSERT INTO TestTable_change_log (update_by, update_time, operation, before_change, after_change)
SELECT USER_NAME(), GETDATE(), 'INSERT', 'N/A' AS before_change,
COALESCE((SELECT i.device_name + ',' + i.device_id + ',' + i.to_table_name
FROM inserted i
FOR XML PATH('')), 'N/A') AS after_change
FROM inserted i;
END
However, my trigger cannot handle multi row insertion like this:
INSERT INTO TestTable
(device_name, device_id, to_table_name)
VALUES
('1', '1', '1'),
('2', '2', '2'),
('3', '3', '3'),
('4', '4', '4')
Output:
+----------+-----------+------------------------+-----------+---------------+----------------------+
| audit_id | update_by | update_time | operation | before_change | after_change |
+----------+-----------+------------------------+-----------+---------------+----------------------+
| 1 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 4,4,43,3,32,2,21,1,1 |
+----------+-----------+------------------------+-----------+---------------+----------------------+
| 2 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 4,4,43,3,32,2,21,1,1 |
+----------+-----------+------------------------+-----------+---------------+----------------------+
| 3 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 4,4,43,3,32,2,21,1,1 |
+----------+-----------+------------------------+-----------+---------------+----------------------+
| 4 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 4,4,43,3,32,2,21,1,1 |
+----------+-----------+------------------------+-----------+---------------+----------------------+
**Expect: **
+----------+-----------+------------------------+-----------+---------------+--------------+
| audit_id | update_by | update_time | operation | before_change | after_change |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 1 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 4,4,4 |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 2 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 3,3,3 |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 3 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 2,2,2 |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 4 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | 1,1,1 |
+----------+-----------+------------------------+-----------+---------------+--------------+
Or this:
INSERT INTO TestTable (device_name) VALUES ('dv99')
Output:
+----------+-----------+------------------------+-----------+---------------+--------------+
| audit_id | update_by | update_time | operation | before_change | after_change |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 1 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | |
+----------+-----------+------------------------+-----------+---------------+--------------+
Expect:
+----------+-----------+------------------------+-----------+---------------+--------------+
| audit_id | update_by | update_time | operation | before_change | after_change |
+----------+-----------+------------------------+-----------+---------------+--------------+
| 1 | user | 1/29/2024 00:00:00.000 | INSERT | N/A | dv99,N/A,N/A |
+----------+-----------+------------------------+-----------+---------------+--------------+
Here are the related tables structure:
CREATE TABLE TestTable_change_log (
audit_id INT IDENTITY(1,1) PRIMARY KEY,
update_by NVARCHAR(128),
update_time DATETIME,
operation NVARCHAR(20),
before_change NVARCHAR(MAX),
after_change NVARCHAR(MAX)
);
CREATE TABLE TestTable (
device_name NVARCHAR(128),
device_id NVARCHAR(128),
to_table_name NVARCHAR(128)
);
Can someone please let me know how should I modify my trigger? I am thinking creating a temp Table and use while loop to insert every row from inserted.
It's unclear why you thought you needed the string aggregation. You can just do a normal INSERT...SELECT....
Normally you would store all the columns separately, but you could use CONCAT_WS to store it in one column.
CREATE OR ALTER TRIGGER trg_insert_TestTable on TestTable
AFTER INSERT
AS
IF @@ROWCOUNT = 0
RETURN; -- early bailout
SET NOCOUNT ON;
INSERT INTO TestTable_change_log
(update_by, update_time, operation, before_change, after_change)
SELECT
USER_NAME(),
GETDATE(),
'INSERT',
NULL AS before_change,
CONCAT_WS(',', i.device_name, i.device_id, i.to_table_name) AS after_change
FROM inserted i;
Another way of storing this information is as separate rows, by unpivoting it. This also allows you to write this as an AFTER INSERT, UPDATE, DELETE trigger
CREATE OR ALTER TRIGGER trg_insert_TestTable on TestTable
AFTER INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0
RETURN; -- early bailout
SET NOCOUNT ON;
INSERT INTO TestTable_change_log
(update_by, update_time, operation, id, before_change, after_change)
SELECT
USER_NAME(),
GETDATE(),
ISNULL(i.id, d.id),
IIF(d.id IS NULL,
'INSERT',
IIF(i.id IS NULL, 'DELETE', 'UPDATE')
),
v.before_change,
v.after_change
FROM inserted i
FULL JOIN deleted d ON d.id = i.id
CROSS APPLY (VALUES
('device_name', CAST(d.device_name AS sql_variant), CAST(i.device_name AS sql_variant)),
('device_id', d.device_id, i.device_id),
('table_name', d.to_table_name, i.table_name)
) v(column_name, before_change, after_change)
WHERE v.before_change IS DISTINCT FROM v.after_change;
-- for older versions of SQL Server use:
-- WHERE NOT EXISTS (SELECT v.before_change INTERSECT SELECT v.after_change)
Or just use Temporal Tables, which already do all this for you.
On a side note: SQL Server now has STRING_AGG so even if you really did need aggregation you could use that.