dbo.QBM_ZRITriggerPrepare

1           create   procedure QBM_ZRITriggerPrepare ( @SlotNumberDummy int , @dummy1 varchar(38) , @dummy2 varchar(38) , @GenProcIDDummy varchar
2(38) ) as begin   declare @Table nvarchar(32) declare @RelationID nvarchar(64) declare @DebugSwitch int = 0 declare @Message nvarchar(1000) declare @GenProcID
3 varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate
4 datetime = getutcdate() BEGIN TRY drop table if exists #Relations create table #Relations (relationid nvarchar(64) collate database_default not null, 
5parenttable nvarchar(64) collate database_default index #Relations1, childtable nvarchar(64) collate database_default , generateChild nvarchar(3) collate
6 database_default, generateParent nvarchar(3) collate database_default primary key (relationid) )  exec QBM_PSessionContextSet 'GenProcID', @GenProcID 
7update QBMRelation set generateParent = '' , XDateUpdated = @Xdate , XUserUpdated = @XUser where generateParent is null  update QBMRelation set generateChild
8 = '' , XDateUpdated = @Xdate , XUserUpdated = @XUser where generateChild is null insert into #Relations (relationid, parenttable, childtable, generateChild
9, generateparent ) select r.relationid, r.parenttable, r.childtable, upper(left(v.generateChild, 3)), upper(left(v.Generateparent ,3)) from QBM_VQBMRelation
10 r join QBM_VRIDefaults v on r.Parentrestriction = v.Parentrestriction and r.Parentexecuteby = v.Parentexecuteby and r.ChildRestriction = v.ChildRestriction
11 and r.ChildExecuteBy = v.ChildExecuteBy  join information_schema.tables tc with (readpast) on r.Childtable = tc.table_name and tc.table_type = 'BASE TABLE'
12  join information_schema.tables tp with (readpast) on r.Parenttable = tp.table_name and tp.table_type in( 'BASE TABLE', 'VIEW')  where v.Generateparent
13 > ' ' or v.GenerateChild > ' '   update #relations set GenerateChild = 'TIR', GenerateParent = ''  where GenerateChild like 'C%' and ParentTable in (select
14 table_name from information_schema.tables with (readpast) where table_type = 'VIEW') update #relations set GenerateParent = ''  where ParentTable in (select
15 table_name from information_schema.tables with (readpast) where table_type = 'VIEW') and GenerateParent > ' '  update #relations set GenerateChild = 'TIR'
16, GenerateParent = 'TDS'  where GenerateChild = 'CDS' and ParentTable = ChildTable update #relations set GenerateChild = 'TIR', GenerateParent = 'TDC' 
17 where GenerateChild = 'CDC' and ParentTable = ChildTable update #relations set GenerateChild = 'TIR', GenerateParent = 'TDR'  where GenerateChild = 'CDR'
18 and ParentTable = ChildTable    update #relations  set GenerateChild = 'TIR', GenerateParent = 'TDS' from #relations r where exists (select top 1 1 from
19 (select substring(Relationbleibt, 16, 100)as RelationID , childtable, parentTable from ( select max(str(isnull(t.CountRows, 0), 15, 0) + relationID) as
20 Relationbleibt, childtable, parentTable, count(*) as CountItems from #relations r join dialogTable t with (readpast) on r.childtable = t.TableName where
21 r.GenerateChild = 'CDS' group by r.childtable, r.parenttable having count(*) > 1 ) as x ) as y where y.childtable = r.childTable and y.ParentTable = r.parenttable
22 and y.relationid <> r.relationid and r.GenerateChild = 'CDS' )  update #relations  set GenerateChild = 'TIR', GenerateParent = 'TDC' from #relations r
23 where exists (select top 1 1 from (select substring(Relationbleibt, 16, 100)as RelationID , childtable, parentTable from ( select max(str(isnull(t.CountRows
24, 0), 15, 0) + relationID) as Relationbleibt, childtable, parentTable, count(*) as CountItems from #relations r join dialogTable t with (readpast) on r.childtable
25 = t.TableName where r.GenerateChild = 'CDC' group by r.childtable, r.parenttable having count(*) > 1 ) as x ) as y where y.childtable = r.childTable and
26 y.ParentTable = r.parenttable and y.relationid <> r.relationid and r.GenerateChild = 'CDC' )    select @RelationID = N'#' while @RelationID is not null
27 begin select @RelationID = null select top 1 @RelationID = c.relationID from #relations c join #relations p on c.parenttable = p.childtable and p.generateChild
28 = 'CDC' and c.GenerateChild = 'CDC' and p.parenttable <> p.childtable order by p.parenttable, p.childtable if @RelationID is not null begin  update #relations
29 set GenerateChild = 'TIR', GenerateParent = 'TDC' where relationID = @RelationID end end   select @RelationID = N'#' while @RelationID is not null begin
30 select @RelationID = null select top 1 @RelationID = c.relationID from #relations c join #relations p on c.parenttable = p.childtable and p.generateChild
31 = 'CDC' and c.GenerateChild = 'CDS' and p.parenttable <> p.childtable order by p.parenttable, p.childtable if @RelationID is not null begin  update #relations
32 set GenerateChild = 'TIR', GenerateParent = 'TDS' where relationID = @RelationID end end update #relations  set GenerateChild = 'TIR', GenerateParent 
33= 'TDC' from #relations r where exists (select top 1 1 from (select substring(Relationbleibt, 4, 100)as RelationID , childtable, parentTable from ( select
34 max( r.GenerateChild + relationID) as Relationbleibt, childtable, parentTable  from #relations r where r.GenerateChild in ( 'CDS', 'CDC') group by r.childtable
35, r.parenttable having count(*) > 1 ) as x ) as y where y.childtable = r.childTable and y.ParentTable = r.parenttable and y.relationid <> r.relationid 
36and r.GenerateChild = 'CDC' )  update #relations set GenerateParent = 'TDX' where GenerateParent = 'TDC' and Parenttable = Childtable   update #relations
37  set GenerateChild = 'TIR', GenerateParent = 'TDO' from #Relations r where ( r.generateChild = 'CDC' or r.generateParent = 'TDC' ) and exists (select 
38top 1 1 from DialogTable t with (readpast) join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable where t.TableName = r.childtable
39 and c.ColumnName = 'XOrigin' ) update #relations  set GenerateChild = 'TIR', GenerateParent = 'TDE' from #Relations r where ( r.generateChild = 'CDC' 
40or r.generateParent = 'TDC' ) and exists (select top 1 1 from DialogTable t with (readpast) join DialogColumn c with (readpast) on t.UID_DialogTable = 
41c.UID_DialogTable where t.TableName = r.childtable and c.ColumnName = 'XIsInEffect' ) insert into #Relations (relationid, parenttable, childtable, generateChild
42, generateparent ) select r.relationid, r.parenttable, r.childtable, '', '' from QBM_VQBMRelation r where Not exists (select top 1 1 from #Relations w 
43where w.relationid = r.relationid ) and (r.generateChild > ' ' or r.generateparent > ' ' )   declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01
44 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select a.childtable as uid from ( select r.* from #relations r join QBMRelation dt
45 with (readpast) on r.relationid = dt.relationid where r.generateChild <> dt.generateChild or r.generateParent <> dt.generateParent ) as a union select
46 b.parenttable from ( select r.* from #relations r join QBMRelation dt with (readpast) on r.relationid = dt.relationid where r.generateChild <> dt.generateChild
47 or r.generateParent <> dt.generateParent ) as b ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonMakeRITrigger', @DBQueueElements_01 update QBMRelation
48 set GenerateChild = x.GenerateChild, GenerateParent = x.GenerateParent , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation join #relations
49 x on QBMRelation.relationID = x.Relationid where QBMRelation.generateChild <> x.generateChild or QBMRelation.generateParent <> x.generateParent END TRY
50 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: truncate table #Relations exec QBM_PSessionContextSet
51 'GenProcID', @GenProcID_R return end 
52