dbo.QBM_TUDialogState

1create   trigger QBM_TUDialogState on DialogState  for Update not for Replication as begin  declare @SQLCmd nvarchar(max) declare @DebugSwitch int
2 = 0 declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select top
3 1 1 from deleted) goto start return start:  if UPDATE(IsInUse) begin if exists (select top 1 1 from deleted d join DialogState x on d.UID_DialogState 
4= x.UID_DialogState where d.IsInUse = 1 and x.IsInUse = 0 ) begin   drop table if exists #Verwaltungseinheit create table #Verwaltungseinheit(UID_PK varchar
5(38) collate database_default)  insert into #Verwaltungseinheit(UID_PK) select d.UID_DialogState from deleted d join DialogState x on d.UID_DialogState
6 = x.UID_DialogState 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 = 'DialogState' and t.TableType
22 in ('B', 'T') and r.IsMNRelation = 0 and t.IsMAllTable = 0  and r.ChildTable not in ('UCIUser'  , 'DialogStateHoliday' )  if @DebugSwitch > 0 begin print
23 @SQLCmd end exec sp_executesql @SQLCmd end  end   if UPDATE(IsInUse) or UPDATE(UID_DialogCountry) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert
24 into @DBQueueElements_01 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogState as uid from DialogState i join deleted
25 d on i.UID_DialogState = d.UID_DialogState where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) or isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry
26, '') ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset2', @DBQueueElements_01 exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset',
27 @DBQueueElements_01 end if UPDATE(IsInUse) or UPDATE(UID_DialogCountry) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02
28 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogCountry as uid from DialogState i join deleted d on i.UID_DialogState
29 = d.UID_DialogState where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) or isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry, '') union select
30 d.UID_DialogCountry as uid from DialogState i join deleted d on i.UID_DialogState = d.UID_DialogState where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 
310) or isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry, '') ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonCountryUTCOffset', @DBQueueElements_02
32 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel:   return end 
33