ILC_BGTaskHistoryAfterUpdate

IF EXISTS(SELECT TOP 1 1 FROM sys.triggers WHERE name = 'ILC_BGTaskHistoryAfterUpdate')
    DROP TRIGGER [dbo].[ILC_BGTaskHistoryAfterUpdate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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)
    DECLARE @Site VARCHAR(8)

    SELECT TOP 1 @Site = [site] FROM parms

    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
                     @Site + ' 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
GO

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