t-sqltriggersauditing

Why doesn't the deleted table in a FOR/AFTER DELETE Trigger contain data of the Deleted Record?


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

Solution

  • 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.