ILC_BGTaskHistoryAfterUpdate

CREATE TRIGGER [dbo].[ILC_BGTaskHistoryAfterUpdate] ON [dbo].[BGTaskHistory]
AFTER UPDATE
AS
DECLARE @CompletionStatusBefore AS VARCHAR(100)
DECLARE @CompletionStatusAfter AS VARCHAR(100)
DECLARE @AlertSubjectParm1 AS VARCHAR(max)
DECLARE @AlertMsgParm1 AS VARCHAR(max)

SELECT @CompletionStatusAfter = ISNULL(CAST(CompletionStatus AS VARCHAR(100)), 'EMPTY')
FROM inserted

IF (@CompletionStatusAfter <> '0')
BEGIN
    SELECT @CompletionStatusBefore = ISNULL(CAST(CompletionStatus AS VARCHAR(100)), 'EMPTY')
    FROM deleted

    IF (@CompletionStatusBefore <> @CompletionStatusAfter)
    BEGIN
        SELECT @AlertSubjectParm1 = AlertSubjectParm1
            ,@AlertMsgParm1 = AlertMsgParm1
        FROM (
            SELECT 'Background Task ' + TaskName + ' has failed' AS AlertSubjectParm1
                ,'Background Task ' + ISNULL(CAST(TaskNumber AS VARCHAR(100)), '') + ' ' + ISNULL(TaskName, '') + ', initiated by user ' + ISNULL(RequestingUser, '') + ' failed at ' + ISNULL(CAST(MONTH(CompletionDate) AS VARCHAR(2)) + '-' + CAST(DAY(CompletionDate) AS VARCHAR(2)) + '-' + CAST(YEAR(CompletionDate) AS VARCHAR(4)) + ' ' + CAST(DATEPART(hh, CompletionDate) AS VARCHAR(2)) + ':' + CAST(DATEPART(MINUTE, CompletionDate) AS VARCHAR(2)), '') + ', with a Completion Status of ' + ISNULL(CAST(CompletionStatus AS VARCHAR(100)), '') + ' and the error message:  ' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + ISNULL(TaskErrorMsg, '') AS AlertMsgParm1
            FROM inserted
            ) NotifyPublicationSubscriber

        EXEC NotifyPublicationSubscribersSp 'ILC_BackgroundTaskFailure'
            ,'D=Text0'
            ,'E=Msg'
            ,''
            ,''
            ,@AlertSubjectParm1
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,@AlertMsgParm1
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
            ,''
    END
END

RETURN
GO

EXEC sp_settriggerorder @triggername = N'[dbo].[ILC_BGTaskHistoryAfterUpdate]'
    ,@order = N'Last'
    ,@stmttype = N'UPDATE'