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'