Sql-server – After update triggers that deal with multi-row updates

sql serversql-server-2012triggerupdate

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated column, timestamp, user, old value and new value.

Triggers work fine with single updates, but when it comes to multi-row updates, it is not working.

My code is like this:

IF (UPDATE(Priority))  
BEGIN
    SET @UpdatedColumn = 'Priority'
    INSERT INTO dbo.AuditTable
        ( [TableName] ,
          [Source] ,
          [RecordId] ,
          [User] ,
          [TimeStamp] ,
          [UpdatedColumn] ,
          [OldValue] ,
          [NewValue]
        )
    SELECT 
        N'BookingItem' , -- TableName - nvarchar(max)
        (SELECT CODE FROM TBL_LEG_SOURCE 
                     INNER JOIN INSERTED INS ON LEG_SOURCE_ID = INS.SourceId) ,
        INS.Id , -- RecordId - bigint
        (SELECT USERNAME FROM INSERTED 
                     INNER JOIN TBL_USER 
                     ON ModifiedById = USER_ID) , -- User - nvarchar(max)
        GETDATE() , -- TimeStamp - datetime
        @UpdatedColumn , -- UpdatedColumn - nvarchar(max)
        DEL.Priority , -- OldValue - nvarchar(max)
        INS.Priority  -- NewValue - nvarchar(max)
    FROM 
        INSERTED INS INNER JOIN DELETED DEL ON INS.Id = DEL.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)
        )
END

Error message:

Msg 512, Level 16, State 1, Procedure MyTrigger, Line 818
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions on how to fix my trigger in order to handle multi-row operations?

Best Answer

Both subqueries in the query you show join to INSERTED without doing an aggregate or a top(1). So both potentially return more than one row. instead of joining to the INSERTED table again, just reference the column directly. With that the second query would look like this:

(SELECT U.USERNAME FROM TBL_USER U WHERE INS.ModifiedById = U.USER_ID)

The change to the other one is similar.

Related Question