Триггер InvoiceStatusTrigger

declare @scriptGuid uniqueidentifier,
		@taskNumber varchar(10),
		@scriptName varchar(150),
		@scriptdescription varchar(max)

set @scriptGuid = 'A7681C28-A62D-4E78-B258-3AE919ECD83B' --для конкретного скрипта присваиваем значение нового гуида
set @taskNumber = 'TMMIS-3333' --присваиваем номер задачи Jira
set @scriptName = 'Администрирование. Триггер. (TMMIS-3333). Изменение статуса счёта после выполнения всех его услуг' --наименование скрипта (краткое описание)
set @scriptdescription = 'Администрирование. Триггер. (TMMIS-3333). Изменение статуса счёта после выполнения всех его услуг' --описание скрипта

if (not exists(select 1 from web_script where [guid] = @scriptGuid))
begin
	insert into web_script(x_edition, x_status, [guid], taskNumber, Name, [description], RunCount, Success)
	select 1, 1, @scriptGuid, @taskNumber, @scriptName, @scriptdescription, 1, 0
end
else
begin
	update web_script
	set RunCount = RunCount + 1
	where [guid] = @scriptGuid
end

/* ----------------- end of: Начало выполнения запроса -------------------------  */
	if exists (select 1 from sys.objects where name = 'InvoiceStatusTrigger' and type = 'TR')
	begin
		drop trigger [dbo].InvoiceStatusTrigger
	end

	go

	create trigger [dbo].InvoiceStatusTrigger on hlt_SMTAP after insert
	as begin

		declare @date datetime = convert(
			datetime,
			(select isnull((select top 1 ValueStr from x_UserSettings where Property = 'Аванс: Дата включения (ДД.ММ.ГГГГ)' and rf_UserID = 1), '01.01.2222')),
			104)

		if (
				((select max(rf_InvoiceID) from inserted) = 0)
				or ((select isnull((select top 1 ValueInt from x_UserSettings where Property = 'Платные услуги: Аванс' and rf_UserID = 1), 0)) = 0)
				or @date > getdate()
		)
		return

		declare @doneStatusId int = (select isnull((select top 1 InvoiceStatusID from hlt_InvoiceStatus where Code = '10'), 0)) --оплачен и оказан
		declare @ofdStatusId int = (select isnull((select top 1 InvoiceStatusID from hlt_InvoiceStatus where Code = '9'), 0)) --требуется отправка в ОФД

		update invoice
			set rf_InvoiceStatusID = (
				select case when count(smt.SMTAPID) >= count(bs.BillServiceId)
				then case when (select isnull((select top 1 ValueInt from x_UserSettings where Property = 'Интеграция с 1С' and rf_UserID = 1), 0)) > 0
					then @ofdStatusId
					else @doneStatusId end
				else invoice.rf_InvoiceStatusID end		
				from hlt_Invoice inv
				join hlt_InvoiceStatus status on inv.rf_InvoiceStatusID = status.InvoiceStatusID and status.Code = '3' --оплачен
				left join hlt_BillService bs on bs.rf_InvoiceID = inv.InvoiceID
				left join hlt_SMTAP smt on smt.rf_InvoiceID = inv.InvoiceID and smt.rf_BillServiceID = bs.BillServiceID
				where inv.InvoiceID = invoice.InvoiceID 
			)
		from hlt_Invoice invoice
		join hlt_InvoiceStatus status on invoice.rf_InvoiceStatusID = status.InvoiceStatusID and status.Code = '3' --оплачен
		where invoice.InvoiceID in (select distinct rf_InvoiceID from inserted) and invoice.Date >= @date
	end

	go
/* ----------------- Конец выполнения запроса ---------------------------------  */

update web_script
set Success = 1
where [guid] = 'A7681C28-A62D-4E78-B258-3AE919ECD83B'

/* ----------------- end of: Конец выполнения ---------------------------------  */