dbo.QBM_TUDialogCountry

1 create   trigger QBM_TUDialogCountry on DialogCountry  for Update not for Replication as begin declare @SQLCmd nvarchar(max) declare @DebugSwitch
2 int = 0 declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select
3 top 1 1 from deleted) goto start return start:  if UPDATE(IsInUse) begin if exists (select top 1 1 from deleted d join DialogCountry x on d.UID_DialogCountry
4 = x.UID_DialogCountry where d.IsInUse = 1 and x.IsInUse = 0 ) begin   drop table if exists #Verwaltungseinheit create table #Verwaltungseinheit(UID_PK
5 varchar(38) collate database_default)  insert into #Verwaltungseinheit(UID_PK) select d.UID_DialogCountry from deleted d join DialogCountry x on d.UID_DialogCountry
6 = x.UID_DialogCountry where d.IsInUse = 1 and x.IsInUse = 0  select top 1 @SQLCmd = CONCAT( 'declare @ElemKey varchar(138) = null
7					declare @Message nvarchar(1000)
8					'
9 , string_agg( convert(nvarchar(max), CONCAT(N'
10					select @ElemKey = null
11					select top 1 @ElemKey = x.XObjectKey
12					from ', r.ChildTable , ' x join #Verwaltungseinheit y on x.'
13, r.ChildColumn ,' = y.UID_PK
14					if @ElemKey > '' ''
15					 begin
16						select @Message = CONCAT( ''#LDS#Changes cannot take place because assignments still exist. Table: {0} Element: {1}.|'' , '''
17, r.ChildTable , ''' , ''|'' , @ElemKey , ''|'' )
18						raiserror( @message, 18, 2) with nowait
19					 end
20					 ' ))  , convert(nvarchar(10), NCHAR(13
21) + nchar(10)) )  )  from QBM_VQBMRelation r join DialogTable t on r.UID_DialogTableChild = t.UID_DialogTable where r.ParentTable = 'DialogCountry' and
22 t.TableType in ('B', 'T') and r.IsMNRelation = 0 and t.IsMAllTable = 0  and r.ChildTable not in ('UCIUser'  , 'DialogState', 'DialogCountryHoliday' ) 
23 if @DebugSwitch > 0 begin print @SQLCmd end exec sp_executesql @SQLCmd end  end   if UPDATE(IsInUse) begin declare @DBQueueElements_01 QBM_YDBQueueRaw
24 insert into @DBQueueElements_01 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogCountry as uid from DialogCountry
25 i join deleted d on i.UID_DialogCountry = d.UID_DialogCountry where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonCountryUTCOffset'
26, @DBQueueElements_01 end  if UPDATE(AvgUTCOffset) or UPDATE(IsDayLightSaving) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02
27 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select s.uid_DialogState as uid from DialogCountry i join deleted d on i.UID_DialogCountry
28 = d.UID_DialogCountry join DialogState s on s.UID_DialogCountry = i.UID_DialogCountry where isnull(i.AvgUTCOffset, 0) <> isnull(d.AvgUTCOffset, 0) or 
29isnull(i.IsDayLightSaving, 0) <> isnull(d.IsDayLightSaving, 0) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset2', @DBQueueElements_02 end
30  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel:  return end 
31