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
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"),
@LogText are of type
@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...