Pasted

IF EXISTS(SELECT TOP 1 1 FROM sys.triggers WHERE name = 'ILC_BGTaskHistory_mstAfterUpdate')
    DROP TRIGGER [dbo].[ILC_BGTaskHistory_mstAfterUpdate] 
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[ILC_BGTaskHistory_mstAfterUpdate]
ON [dbo].[BGTaskHistory_mst]
AFTER Update
AS
    DECLARE @SiteRef dbo.SiteType = dbo.GetSiteContext()
 
    DECLARE @CompletionStatusBefore as varchar(100)
    DECLARE @CompletionStatusAfter as varchar(100)
    DECLARE @AlertSubjectParm1 as varchar(max)
    DECLARE @AlertMsgParm1 as varchar(max)
    DECLARE @ConfigName varchar(8)
 
    SELECT TOP 1 @ConfigName = [dbo].[ILC_GetConfigurationName]()
 
    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 
                     @ConfigName + ' 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 dbo.NotifyPublicationSubscribersSp 
                @PublicationName        = 'ILC_BackgroundTaskFailure'               
                ,@AlertSubject          = 'D=Text0'
                ,@AlertMsg              = 'E=Msg'
                ,@AlertHTMLMsg          = NULL  -- For GenericSendEmail: @AlertHTMLMsg overrides @AlertMsg; For GenericNotify: Both can be used.
                ,@AlertSubjectParm1     = @AlertSubjectParm1
                ,@AlertMsgParm1         = @AlertMsgParm1
                ,@Category              = 'IT Admin'
        END
    END
 
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ILC_GetConfigurationName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[ILC_GetConfigurationName]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[ILC_GetConfigurationName] 
(   
)
RETURNS SiteType 
AS
BEGIN
    DECLARE @ReturnValue SiteType
    
    DECLARE @DBName nvarchar(16) = DB_NAME() 
    DECLARE @SiteRef dbo.SiteType = (select [site] from parms where parm_key = 0)
 
    SELECT @ReturnValue = SUBSTRING(@DBName,1,3) + SUBSTRING(@SiteRef,4,LEN(@SiteRef) - 3) 
    
    RETURN @ReturnValue
 
END