0

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
Matt Arnold
  • 668
  • 2
  • 8
  • 21
  • Oh - I see why that didn't work now! I'm reading back from the table in its new state! I was joining to it as a safety precaution (I've done the same thing for some UPDATE and INSERT triggers) as I have previously just used those tables directly to modify the table in question and they updated everything in the target table! It's another problem though so I'll raise a separate question for it if needed. – Matt Arnold Feb 02 '22 at 16:27
  • 1
    You might also look at Temporal Tables, if you haven't already, as this provides automatic auditing of updates and deletes out-of-the-box. – Stu Feb 02 '22 at 16:32
  • Do you know whether this is possible without Azure? I've had a quick search for Temporal Tables and everything seems to suggest to use an Azure SQL Server Managed Instance. – Matt Arnold Feb 02 '22 at 16:55
  • System versioning is part of the ANSI SQL Standard, all supported versions of SQL Server implement it, although I haven't used Single azure database instances which do have limitations - although [this](https://learn.microsoft.com/en-us/azure/azure-sql/temporal-tables) suggests you can. – Stu Feb 02 '22 at 16:56

1 Answers1

2

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.

Stu
  • 30,392
  • 6
  • 14
  • 33