S
Here is how to fix the errors, using proper joins (and if this isn't "fast enough" then look at your indexing):
INSERT dbo.AuditTable
(
[TableName],
[Source],
[RecordId],
[User],
[TimeStamp],
[UpdatedColumn],
[OldValue],
[NewValue]
)
SELECT
N'BookingItem', -- TableName - nvarchar(max)
ls.CODE,
INS.Id, -- RecordId - bigint
u.USERNAME,
GETDATE(), -- TimeStamp - datetime
@UpdatedColumn, -- UpdatedColumn - nvarchar(max)
DEL.Priority, -- OldValue - nvarchar(max)
INS.Priority -- NewValue - nvarchar(max)
FROM
INSERTED AS INS
INNER JOIN
DELETED AS DEL ON INS.Id = DEL.Id
INNER JOIN
dbo.TBL_LEG_SOURCE AS ls ON ls.LEG_SOURCE_ID = INS.SourceId
INNER JOIN
dbo.TBL_USER AS u ON INS.ModifiedById = u.USER_ID
WHERE
(
(INS.Priority <> DEL.Priority)
OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
);
However I think it is quite foolish to run 50+ different inserts of this variety in order to capture every single column change. Why not just create a table with columns for time and table name (you don't need to store username because you can always look that up later), and then whenever there is an update, store the old and new version of the row? You can even use a SEQUENCE to ensure that you can identify the set of rows that were modified together (since timestamp may not be unique enough to do that).
CREATE SEQUENCE dbo.AuditSequence
AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE dbo.AuditData
(
AuditSequenceID INT,
TableName SYSNAME,
[TimeStamp] DATETIME,
RowState CHAR(1), -- e.g. 'B' = before, 'A' = after
... all your 50 columns, including ModifidById ...
);
Now in your trigger:
CREATE TRIGGER dbo.MyTrigger
ON dbo.BookingItem
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @as INT = NEXT VALUE FOR dbo.AuditSequence,
@now DATETIME = CURRENT_TIMESTAMP;
INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
... the rest of your 50 columns)
SELECT @as, N'BookingItem', @now, 'B', * FROM deleted;
INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
... the rest of your 50 columns)
SELECT @as, N'BookingItem', @now, 'A', * FROM inserted;
END
GO
Now, write complicated queries against this much simpler audit structure that are inefficient and try to track exactly which columns have changed and all of that. Much better to pay that price when you're reviewing audit data than to pay that price on every single update operation.
