Sql-server – Audit Trail trigger can’t detect if dates are the same

auditsql servertriggerupdate

I know this is a common problem with SQL language triggers, but I just cannot seem to get this right.

Basically I'm trying to write an "Audit Trail" trigger for all of my fields. I'm using an IF UPDATE()... branching scheme which, as I've discovered on other SO posts, is not a reliable function.

My problem is centered around a date field that may be NULL. I'm simply trying to write a trigger that will only write to my log table if the date field has changed (either in value or to/from NULL)

Right now my code in this branch is as follows, and will always fire no matter if the dates didn't change (but other fields did):

IF UPDATE(DueDate)
BEGIN
    DECLARE @insertedDate date
    DECLARE @deletedDate date

    SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
    FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate

    IF (@insertedDate = @deletedDate) 
    BEGIN
        return
    END

    SET @IsModified = 1
    SET @LogText += ('Due: ')
    SELECT @TempText = '"' + ISNULL(convert(nvarchar(255), i.DueDate, 107), '[deleted]') + '" '
    FROM inserted i 
    SET @LogText += @TempText
    SET @TempText = ''
    SELECT @TempText = '(was: "' + ISNULL(convert(nvarchar(255), d.DueDate, 107),'[not set]') + '"), '
    FROM deleted d
    SET @LogText += @TempText
    SET @TempText = ''

END

Which will incorrectly produce (instead of not running at all):

Due: "Mar 04, 2020" (was: "Mar 04, 2020"),

Both @Temptext and @LogText are of type nvarchar(MAX); and @LogText is a running string which contains the final log line to be written (and will concat through the whole procedure with +=)

Is there anything I'm missing with how I'm constructing these conditionals? I've tried several iterations and function methods but this one is just not making sense to me…

Best Answer

This part of your trigger is totally wrong:

DECLARE @insertedDate date
DECLARE @deletedDate date

SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate

IF (@insertedDate = @deletedDate) 
BEGIN
    return
END

First error here is that you use cross join instead of inner join to join inserted and deleted tables, you should join them on PK of your table.

As the result, when you update more than one row at a time, you'll find rows where d.DueDate <> i.DueDate just because they refer to different PK values.

I make you an example to show this.

Suppose your table is this one:

create table dbo.duedate(id int primary key, DueDate datetime);
insert into dbo.duedate values(1, '20170101'), (2, '20170202');

So you have 2 rows with 2 different dates. Now you update your PK like this:

update dbo.duedate
set id = id + 1

Your trigger now goes to look inserted and deleted tables, but instead of join them on id and have 2 result rows it makes cross join so you have 4 rows as the result of join and every row from inserted is in match with every row from deleted, and you find 2 rows where inserted.DueDate <> deleted.DueDate, exactly, 20170101 <> 20170202 and 20170202 <> 20170101, even if you did not touch any DueDate at all in your update, and this is wrong.

The second error is that you assign multiple values to your variables:

SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
FROM @inserted i, @deleted d WHERE d.DueDate <> i.DueDate

In my example above you assign 2 values to @insertedDate and 2 to @deletedDate. You have no server error about it but the result of such assignment is unpredictable.

So rewrite your trigger joining inserted and deleted on table PK and have in mind that when you assigning a set of values to a variable, you get no error but only last value will be reflected in your variable and this is not what you want

Related Question