ALTER trigger [dbo].[TR_LicHistItem_LogChanges] on [dbo].[tblLicHistItem] after insert,update,delete as
begin
	set nocount on
	begin try
		declare @c varbinary(12)
		declare @d varbinary(12),@u varbinary(4)
		select @c=context_info()
		declare @PersonId int=null, @Date datetime = null
		select @PersonId=cast(substring(@c,1,4) as int)
		select @Date=cast(substring(@c,5,8) as datetime)
		select @PersonId=iPersonId from tPerson where bPersonSystem=1 and @PersonId is null
		select @Date=GETUTCDATE() where @Date is null or @Date = 0

		insert into LicHistLog (LicHistId,LicHistLogKindId,PersonId,Message)
		select
			i.lintLicHistId,
			llk.id,
			@PersonId,
			replace(llk.MsgTemplate,'$1',
				isnull(n.txtName,'-')+'; '
					+ case when isnull(i.ActivationKey,'')='' then '-' else i.ActivationKey end + '; '
					+ case when isnull(i.txtDepartment,'')='' then '-' else i.txtDepartment end + '; '
					+ case when isnull(i.txtInventoryNum,'')='' then '-' else i.txtInventoryNum end + '; '
					+ case when isnull(i.txtRequest,'')='' then '-' else i.txtRequest end)
		from inserted i
			join LicHistLogKind llk on llk.id=7
			left join tblNode n on n.intNodeId=i.NodeId
			left join deleted d on d.intLicHistItemId=i.intLicHistItemId
		where d.intLicHistItemId is null and i.lintCancelledByLicHistId is null

		insert into LicHistLog (LicHistId,LicHistLogKindId,PersonId,Message)
		select
			i.lintLicHistId,
			llk.id,
			@PersonId,
			replace(llk.MsgTemplate,'$1',
				isnull(n.txtName,'-')+'; '
					+ case when isnull(i.ActivationKey,'')='' then '-' else i.ActivationKey end + '; '
					+ case when isnull(i.txtDepartment,'')='' then '-' else i.txtDepartment end + '; '
					+ case when isnull(i.txtInventoryNum,'')='' then '-' else i.txtInventoryNum end + '; '
					+ case when isnull(i.txtRequest,'')='' then '-' else i.txtRequest end)
		from inserted i
			join LicHistLogKind llk on llk.id=8
			join deleted d on d.intLicHistItemId=i.intLicHistItemId
			left join tblNode n on n.intNodeId=i.NodeId
		where d.lintCancelledByLicHistId is null and i.lintCancelledByLicHistId is not null

		waitfor delay '0:00:00.002'	
				declare @defTZ nvarchar(100)=null
		select @defTZ=sPropertyValue from tProperty where sProperty='DefaultTimeZone'
		if(@defTZ is null)
		begin
			set @defTZ='Central Europe Standard Time'
		end

				declare @fmt int
		select @fmt=case when iDbLocaleId=1033 then 102 else 104 end from tDb

		insert into LicHistItemAssign (AssignedDate,AssignedPersonId,LicHistId,LicHistItemId,LicInfo,ItemInfo,SrcNodeId,DestNodeId)
		select
			@Date,
			@PersonId,
			coalesce(i.lintLicHistId,d.lintLicHistId),
			i.intLicHistItemId,
			left(case when isnull(lh.txtLicName,'')='' then '-' else lh.txtLicName end +'; '
				+ case when isnull(lh.dteDate,'')='' then '-' else convert(nvarchar,dbo.fnLocalTime(lh.dteDate,@defTZ),@fmt) end +'; ' 
				+ case when isnull(lh.intCountChange,'')='' then '-' else cast(lh.intCountChange as nvarchar) end +'; '
				+ case when isnull(lh.txtInventoryNum,'')='' then '-' else lh.txtInventoryNum end +';',1000),
			left(case when isnull(n.txtName,'')='' then '-' else n.txtName end +'; '
				+ case when isnull(d.ActivationKey,'')='' then '-' else d.ActivationKey end +'; '
				+ case when isnull(d.txtDepartment,'')='' then '-' else d.txtDepartment end +'; '
				+ case when isnull(d.txtInventoryNum,'')='' then '-' else d.txtInventoryNum end +'; '
				+ case when isnull(d.txtRequest,'')='' then '-' else d.txtRequest end +'; ',1000),
			d.NodeId,
			i.NodeId
		from inserted i
			full join deleted d on d.intLicHistItemId=i.intLicHistItemId
			join tblLicHist lh on lh.intLicHistId=coalesce(i.lintLicHistId,d.lintLicHistId)
			left join tblNode n on n.intNodeId=d.NodeId
		where i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null
			and isnull(i.NodeId,0)!=isnull(d.NodeId,0)

		waitfor delay '0:00:00.002'	

		insert into LicHistLog (LicHistId,LicHistLogKindId,PersonId,Message)
		select
			d.lintLicHistId,
			llk.id,
			@PersonId,
			replace(llk.MsgTemplate,'$1',
				isnull(n.txtName,'-')+'; '
					+ case when isnull(d.ActivationKey,'')='' then '-' else d.ActivationKey end + '; '
					+ case when isnull(d.txtDepartment,'')='' then '-' else d.txtDepartment end + '; '
					+ case when isnull(d.txtInventoryNum,'')='' then '-' else d.txtInventoryNum end + '; '
					+ case when isnull(d.txtRequest,'')='' then '-' else d.txtRequest end)
		from deleted d
			join LicHistLogKind llk on llk.id=9
			left join tblNode n on n.intNodeId=d.NodeId
			left join inserted i on i.intLicHistItemId=d.intLicHistItemId
		where i.intLicHistItemId is null and d.lintCancelledByLicHistId is null
		
		insert into LicHistLog (LicHistId,LicHistLogKindId,PersonId,Message,PropName,OldVal,NewVal)
		select
			chngs.lintLicHistId,
			llk.id,
			@PersonId,
			replace(replace(replace(replace(llk.MsgTemplate,'$1',
				isnull(n.txtName,'-')+'; '
					+ case when isnull(d.ActivationKey,'')='' then '-' else d.ActivationKey end + '; '
					+ case when isnull(d.txtDepartment,'')='' then '-' else d.txtDepartment end + '; '
					+ case when isnull(d.txtInventoryNum,'')='' then '-' else d.txtInventoryNum end + '; '
					+ case when isnull(d.txtRequest,'')='' then '-' else d.txtRequest end),
				'$2',c.sTitle),'$3',chngs.OldVal),'$4',chngs.NewVal),
			left(c.sTitle,100),
			left(chngs.OldVal,255),
			left(chngs.NewVal,255)
		from (
				select
					i.lintLicHistId,
					d.intLicHistItemId,
					'ActivationKey' Col,
					isnull(d.ActivationKey,'') OldVal,
					isnull(i.ActivationKey,'') NewVal
				from inserted i
					left join deleted d on d.intLicHistItemId=i.intLicHistItemId
				where isnull(i.ActivationKey,'')!=isnull(d.ActivationKey,'')
					and i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null

				union all

				select
					i.lintLicHistId,
					d.intLicHistItemId,
					'txtMemo' col,
					isnull(d.txtMemo,''),
					isnull(i.txtMemo,'')
				from inserted i
					left join deleted d on d.intLicHistItemId=i.intLicHistItemId
				where isnull(i.txtMemo,'')!=isnull(d.txtMemo,'')
					and i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null

				union all

				select
					i.lintLicHistId,
					d.intLicHistItemId,
					'txtInventoryNum' col,
					isnull(d.txtInventoryNum,''),
					isnull(i.txtInventoryNum,'')
				from inserted i
					left join deleted d on d.intLicHistItemId=i.intLicHistItemId
				where isnull(i.txtInventoryNum,'')!=isnull(d.txtInventoryNum,'')
					and i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null
	
				union all

				select
					i.lintLicHistId,
					d.intLicHistItemId,
					'txtDepartment' col,
					isnull(d.txtDepartment,''),
					isnull(i.txtDepartment,'')
				from inserted i
					left join deleted d on d.intLicHistItemId=i.intLicHistItemId
				where isnull(i.txtDepartment,'')!=isnull(d.txtDepartment,'')
					and i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null

				union all

				select
					i.lintLicHistId,
					d.intLicHistItemId,
					'txtRequest' col,
					isnull(d.txtRequest,''),
					isnull(i.txtRequest,'')
				from inserted i
					left join deleted d on d.intLicHistItemId=i.intLicHistItemId
				where isnull(i.txtRequest,'')!=isnull(d.txtRequest,'')
					and i.lintCancelledByLicHistId is null and d.lintCancelledByLicHistId is null
			) chngs
			join tColumn c on c.sTable='tblLicHistItem' and c.sColumn=chngs.Col
			join LicHistLogKind llk on llk.id=10
			left join deleted d on d.intLicHistItemId=chngs.intLicHistItemId
			left join tblNode n on n.intNodeId=d.NodeId
	end try
	begin catch
		declare @err nvarchar(max)
		select @err=ERROR_MESSAGE()
		raiserror(@err,16,1)
		return
	end catch
end