Given the following audit table:
CREATE TABLE Auditing.[Record Deletion Times]
(
ID BIGINT IDENTITY NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(256) NOT NULL,
[Record ID] INT NOT NULL,
[Date-Time] DATETIME2 NOT NULL,
[Record Information] NVARCHAR(1024) NULL,
CONSTRAINT [Record Deleted Times Primary Key] PRIMARY KEY CLUSTERED
(
ID ASC
),
CONSTRAINT [Record Deleted Modified Times Unique Key] UNIQUE NONCLUSTERED
(
[Schema] ASC,
[Table] ASC,
[Record ID] ASC,
[Date-Time] ASC
)
)
and the following data table:
CREATE TABLE [dbo].[Items]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Key] [varchar](50) NOT NULL,
[Value] [varchar](255) NOT NULL
)
I want a trigger that will record information to Record Deletion Times
when it is deleted from Items
. I have found that I am able to do this with an INSTEAD OF
trigger (whereby I record the data then manually delete the record in the trigger) but was wondering why a FOR
or AFTER
trigger does not do the same thing without the need for me performing the deletion myself. My guess is that deleted
doesn't contain any data about the record that was deleted once it was deleted (what's the best way to debug a trigger?).
This is the trigger I hoped to use which failed to record anything in Record Deletion Times
:
CREATE TRIGGER [Items Deleted]
ON Items
FOR DELETE
AS
INSERT INTO Auditing.[Record Deletion Times]
SELECT
'dbo',
'Items',
deleted.ID,
GETUTCDATE(),
CONCAT
(
'Key: ''',
Items.Key,
''' Value: ''',
Items.Value,
''''
)
FROM Items
JOIN deleted ON deleted.ID = Items.ID
This is the trigger I ended-up using instead:
CREATE TRIGGER [Items Deleted]
ON Items
INSTEAD OF DELETE
AS
BEGIN
INSERT INTO Auditing.[Record Deletion Times]
SELECT
'dbo',
'Items',
deleted.ID,
GETUTCDATE(),
CONCAT
(
'Key: ''',
Items.Key,
''' Value: ''',
Items.Value,
''''
)
FROM Items
JOIN deleted ON deleted.ID = Items.ID
DELETE Items
FROM Items
JOIN deleted ON deleted.ID = Items.ID
END
You should not be selecting from your Items
table since the row(s) you want are now deleted.
Just select from Deleted
Ie
INSERT INTO RecordDeletionTimes
SELECT
'dbo',
'Items',
ID,
GETUTCDATE(),
CONCAT
(
'Key: ''',
[Key],
''' Value: ''',
Value,
''''
)
FROM deleted ;
See Demo Fiddle
The best way to debug your trigger is to just select * from deleted
and test in SSMS using a begin tran/rollback.