Back to OIM Explorer

dbo.QBM_ZRITriggerPrepare

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QBM-K-CommonMakeRITrigger / QBM_ZRITriggerCreate at line 47; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 7.860 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-CommonMakeRITrigger / QBM_ZRITriggerCreate at line 47
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZRITriggerCreate at line 47 Bulk DBQueue insert -> QBM-K-CommonMakeRITrigger / QBM_ZRITriggerCreate at line 47
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonMakeRITrigger -> QBM_ZRITriggerCreate QBM_PDBQueueInsert_Bulk 'QBM-K-CommonMakeRITrigger', @DBQueueElements_01 update QBMRelation set GenerateChild = x.GenerateChild, GenerateParent = x.GenerateParent , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation join #relat…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL323 lines
1CREATE PROCEDURE QBM_ZRITriggerPrepare(2  @SlotNumberDummy int,3  @dummy1 varchar(38),4  @dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @Table nvarchar(32)10  DECLARE @RelationID nvarchar(64)11  DECLARE @DebugSwitch int = 012  DECLARE @Message nvarchar(1000)13  DECLARE @GenProcID varchar(38) = newid()14  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')15  DECLARE @XUser nvarchar(64) = object_name(@@procid)16  DECLARE @Xdate datetime = getutcdate()17  BEGIN TRY18    drop TABLE19    IF EXISTS #Relations20    CREATE TABLE #Relations(21      relationid nvarchar(64) collate database_default NOT NULL,22      parenttable nvarchar(64) collate database_default index #Relations1,23      childtable nvarchar(64) collate database_default,24      generateChild nvarchar(3) collate database_default,25      generateParent nvarchar(3) collate database_default primary key(relationid)26    )27    EXEC QBM_PSessionContextSet 'GenProcID',28      @GenProcID29    UPDATE QBMRelation30    SET generateParent = '',31    XDateUpdated = @Xdate,32    XUserUpdated = @XUser33    WHERE34      generateParent IS NULL35    UPDATE QBMRelation36    SET generateChild = '',37    XDateUpdated = @Xdate,38    XUserUpdated = @XUser39    WHERE40      generateChild IS NULL41    INSERT INTO #Relations(relationid,42    parenttable,43    childtable,44    generateChild,45    generateparent46  )47  SELECT48    r.relationid,49    r.parenttable,50    r.childtable,51    upper(52    LEFT(v.generateChild,53    3)54    ),55    upper(56    LEFT(v.Generateparent,57    3)58    )59FROM QBM_VQBMRelation r60JOIN QBM_VRIDefaults v61  ON r.Parentrestriction = v.Parentrestriction AND r.Parentexecuteby = v.Parentexecuteby AND r.ChildRestriction = v.ChildRestriction AND r.ChildExecuteBy62= v.ChildExecuteBy63JOIN information_schema.tables tc64  WITH(readpast65)66  ON r.Childtable = tc.table_name AND tc.table_type = 'BASE TABLE'67JOIN information_schema.tables tp68  WITH(readpast69)70  ON r.Parenttable = tp.table_name AND tp.table_type IN('BASE TABLE',71'VIEW'72)73WHERE74  v.Generateparent > ' ' OR v.GenerateChild > ' '75UPDATE #relations76SET GenerateChild = 'TIR',77GenerateParent = ''78WHERE79  GenerateChild LIKE 'C%' AND ParentTable IN(80SELECT table_name81FROM information_schema.tables82  WITH(readpast)83WHERE84  table_type = 'VIEW'85)86UPDATE #relations87SET GenerateParent = ''88WHERE89  ParentTable IN(90SELECT table_name91FROM information_schema.tables92  WITH(readpast)93WHERE94  table_type = 'VIEW'95) AND GenerateParent > ' '96UPDATE #relations97SET GenerateChild = 'TIR',98GenerateParent = 'TDS'99WHERE100  GenerateChild = 'CDS' AND ParentTable = ChildTable101UPDATE #relations102SET GenerateChild = 'TIR',103GenerateParent = 'TDC'104WHERE105  GenerateChild = 'CDC' AND ParentTable = ChildTable106UPDATE #relations107SET GenerateChild = 'TIR',108GenerateParent = 'TDR'109WHERE110  GenerateChild = 'CDR' AND ParentTable = ChildTable111UPDATE #relations112SET GenerateChild = 'TIR',113GenerateParent = 'TDS'114FROM #relations r115WHERE116  EXISTS(117SELECT TOP 1 1118FROM(119SELECT120  substring(Relationbleibt,121  16,122  100)123  AS124  RelationID, childtable, parentTable125FROM(126SELECT127  max(str(isnull(t.CountRows, 0), 15, 0) + relationID) AS Relationbleibt, childtable, parentTable,128  count(*) AS CountItems129FROM #relations r130JOIN dialogTable t131  WITH(readpast)132  ON r.childtable = t.TableName133WHERE134  r.GenerateChild = 'CDS'135GROUP BY r.childtable, r.parenttable136HAVING count(*) > 1) AS x) AS y137WHERE138  y.childtable = r.childTable AND y.ParentTable = r.parenttable AND y.relationid <> r.relationid AND r.GenerateChild = 'CDS')139UPDATE #relations140SET GenerateChild = 'TIR',141GenerateParent = 'TDC'142FROM #relations r143WHERE144  EXISTS(145SELECT TOP 1 1146FROM(147SELECT148  substring(Relationbleibt, 16, 100) AS RelationID, childtable, parentTable149FROM(150SELECT151  max(str(isnull(t.CountRows, 0), 15, 0) + relationID) AS Relationbleibt, childtable, parentTable,152  count(*) AS CountItems153FROM #relations r154JOIN dialogTable t155  WITH(readpast)156  ON r.childtable = t.TableName157WHERE158  r.GenerateChild = 'CDC'159GROUP BY r.childtable, r.parenttable160HAVING count(*) > 1) AS x) AS y161WHERE162  y.childtable = r.childTable AND y.ParentTable = r.parenttable AND y.relationid <> r.relationid AND r.GenerateChild = 'CDC')163SELECT @RelationID = N '#'164WHILE @RelationID IS NOT NULL165BEGIN166  SELECT @RelationID = NULL167  SELECT TOP 1 @RelationID = c.relationID168  FROM #relations c169  JOIN #relations p170    ON c.parenttable = p.childtable AND p.generateChild = 'CDC' AND c.GenerateChild = 'CDC' AND p.parenttable <> p.childtable171  ORDER BY p.parenttable,172  p.childtable173  IF @RelationID IS NOT NULL174  BEGIN175    UPDATE #relations176    SET GenerateChild = 'TIR',177    GenerateParent = 'TDC'178    WHERE179      relationID = @RelationID180  END181END182SELECT @RelationID = N '#'183WHILE @RelationID IS NOT NULL184BEGIN185  SELECT @RelationID = NULL186  SELECT TOP 1 @RelationID = c.relationID187  FROM #relations c188  JOIN #relations p189    ON c.parenttable = p.childtable AND p.generateChild = 'CDC' AND c.GenerateChild = 'CDS' AND p.parenttable <> p.childtable190  ORDER BY p.parenttable,191  p.childtable192  IF @RelationID IS NOT NULL193  BEGIN194    UPDATE #relations195    SET GenerateChild = 'TIR',196    GenerateParent = 'TDS'197    WHERE198      relationID = @RelationID199  END200END201UPDATE #relations202SET GenerateChild = 'TIR',203GenerateParent = 'TDC'204FROM #relations r205WHERE206  EXISTS(207SELECT TOP 1 1208FROM(209SELECT210  substring(Relationbleibt, 4, 100) AS RelationID, childtable, parentTable211FROM(212SELECT213  max(r.GenerateChild + relationID) AS Relationbleibt, childtable, parentTable214FROM #relations r215WHERE216  r.GenerateChild IN('CDS', 'CDC')217GROUP BY r.childtable, r.parenttable218HAVING count(*) > 1) AS x) AS y219WHERE220  y.childtable = r.childTable AND y.ParentTable = r.parenttable AND y.relationid <> r.relationid AND r.GenerateChild = 'CDC')221UPDATE #relations222SET GenerateParent = 'TDX'223WHERE224  GenerateParent = 'TDC' AND Parenttable = Childtable225UPDATE #relations226SET GenerateChild = 'TIR',227GenerateParent = 'TDO'228FROM #Relations r229WHERE230  (r.generateChild = 'CDC' OR r.generateParent = 'TDC') AND EXISTS(231SELECT TOP 1 1232FROM DialogTable t233  WITH(readpast)234JOIN DialogColumn c235  WITH(readpast)236  ON t.UID_DialogTable = c.UID_DialogTable237WHERE238  t.TableName = r.childtable AND c.ColumnName = 'XOrigin')239UPDATE #relations240SET GenerateChild = 'TIR',241GenerateParent = 'TDE'242FROM #Relations r243WHERE244  (r.generateChild = 'CDC' OR r.generateParent = 'TDC') AND EXISTS(245SELECT TOP 1 1246FROM DialogTable t247  WITH(readpast)248JOIN DialogColumn c249  WITH(readpast)250  ON t.UID_DialogTable = c.UID_DialogTable251WHERE252  t.TableName = r.childtable AND c.ColumnName = 'XIsInEffect')253INSERT INTO #Relations(relationid,254parenttable,255childtable,256generateChild,257generateparent)258SELECT259  r.relationid,260  r.parenttable,261  r.childtable,262  '',263  ''264FROM QBM_VQBMRelation r265WHERE266  NOT EXISTS(267SELECT TOP 1 1268FROM #Relations w269WHERE270  w.relationid = r.relationid) AND(r.generateChild > ' ' OR r.generateparent > ' ')271DECLARE @DBQueueElements_01 QBM_YDBQueueRaw272INSERT INTO @DBQueueElements_01(object,273subobject,274genprocid)275SELECT276  x.uid,277  NULL,278  @GenProcID279FROM(280SELECT a.childtable AS uid281FROM(282SELECT r.*283FROM #relations r284JOIN QBMRelation dt285  WITH(readpast)286  ON r.relationid = dt.relationid287WHERE288  r.generateChild <> dt.generateChild OR r.generateParent <> dt.generateParent) AS a289UNION290SELECT b.parenttable291FROM(292SELECT r.*293FROM #relations r294JOIN QBMRelation dt295  WITH(readpast)296  ON r.relationid = dt.relationid297WHERE298  r.generateChild <> dt.generateChild OR r.generateParent <> dt.generateParent) AS b) AS x299EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonMakeRITrigger',300  @DBQueueElements_01301UPDATE QBMRelation302SET GenerateChild = x.GenerateChild,303GenerateParent = x.GenerateParent,304XDateUpdated = @Xdate,305XUserUpdated = @XUser306FROM QBMRelation307JOIN #relations x308  ON QBMRelation.relationID = x.Relationid309WHERE310  QBMRelation.generateChild <> x.generateChild OR QBMRelation.generateParent <> x.generateParent311END TRY312BEGIN CATCH313  EXEC QBM_PSessionErrorAdd DEFAULT314  RAISERROR('',315  18,316  1)317    WITH NOWAIT318END CATCH319ende: truncate TABLE #Relations320EXEC QBM_PSessionContextSet 'GenProcID',321  @GenProcID_R322RETURN323END
Open raw exported source
SQL · Raw52 lines
1           create   procedure QBM_ZRITriggerPrepare ( @SlotNumberDummy int , @dummy1 varchar(38) , @dummy2 varchar(38) , @GenProcIDDummy varchar2(38) ) as begin   declare @Table nvarchar(32) declare @RelationID nvarchar(64) declare @DebugSwitch int = 0 declare @Message nvarchar(1000) declare @GenProcID3 varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate4 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) collate6 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 generateChild8 = '' , XDateUpdated = @Xdate , XUserUpdated = @XUser where generateChild is null insert into #Relations (relationid, parenttable, childtable, generateChild9, generateparent ) select r.relationid, r.parenttable, r.childtable, upper(left(v.generateChild, 3)), upper(left(v.Generateparent ,3)) from QBM_VQBMRelation10 r join QBM_VRIDefaults v on r.Parentrestriction = v.Parentrestriction and r.Parentexecuteby = v.Parentexecuteby and r.ChildRestriction = v.ChildRestriction11 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.Generateparent13 > ' ' or v.GenerateChild > ' '   update #relations set GenerateChild = 'TIR', GenerateParent = ''  where GenerateChild like 'C%' and ParentTable in (select14 table_name from information_schema.tables with (readpast) where table_type = 'VIEW') update #relations set GenerateParent = ''  where ParentTable in (select15 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 from19 (select substring(Relationbleibt, 16, 100)as RelationID , childtable, parentTable from ( select max(str(isnull(t.CountRows, 0), 15, 0) + relationID) as20 Relationbleibt, childtable, parentTable, count(*) as CountItems from #relations r join dialogTable t with (readpast) on r.childtable = t.TableName where21 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.parenttable22 and y.relationid <> r.relationid and r.GenerateChild = 'CDS' )  update #relations  set GenerateChild = 'TIR', GenerateParent = 'TDC' from #relations r23 where exists (select top 1 1 from (select substring(Relationbleibt, 16, 100)as RelationID , childtable, parentTable from ( select max(str(isnull(t.CountRows24, 0), 15, 0) + relationID) as Relationbleibt, childtable, parentTable, count(*) as CountItems from #relations r join dialogTable t with (readpast) on r.childtable25 = t.TableName where r.GenerateChild = 'CDC' group by r.childtable, r.parenttable having count(*) > 1 ) as x ) as y where y.childtable = r.childTable and26 y.ParentTable = r.parenttable and y.relationid <> r.relationid and r.GenerateChild = 'CDC' )    select @RelationID = N'#' while @RelationID is not null27 begin select @RelationID = null select top 1 @RelationID = c.relationID from #relations c join #relations p on c.parenttable = p.childtable and p.generateChild28 = 'CDC' and c.GenerateChild = 'CDC' and p.parenttable <> p.childtable order by p.parenttable, p.childtable if @RelationID is not null begin  update #relations29 set GenerateChild = 'TIR', GenerateParent = 'TDC' where relationID = @RelationID end end   select @RelationID = N'#' while @RelationID is not null begin30 select @RelationID = null select top 1 @RelationID = c.relationID from #relations c join #relations p on c.parenttable = p.childtable and p.generateChild31 = 'CDC' and c.GenerateChild = 'CDS' and p.parenttable <> p.childtable order by p.parenttable, p.childtable if @RelationID is not null begin  update #relations32 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 ( select34 max( r.GenerateChild + relationID) as Relationbleibt, childtable, parentTable  from #relations r where r.GenerateChild in ( 'CDS', 'CDC') group by r.childtable35, 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 #relations37  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.childtable39 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, generateChild42, 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_0144 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select a.childtable as uid from ( select r.* from #relations r join QBMRelation dt45 with (readpast) on r.relationid = dt.relationid where r.generateChild <> dt.generateChild or r.generateParent <> dt.generateParent ) as a union select46 b.parenttable from ( select r.* from #relations r join QBMRelation dt with (readpast) on r.relationid = dt.relationid where r.generateChild <> dt.generateChild47 or r.generateParent <> dt.generateParent ) as b ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonMakeRITrigger', @DBQueueElements_01 update QBMRelation48 set GenerateChild = x.GenerateChild, GenerateParent = x.GenerateParent , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation join #relations49 x on QBMRelation.relationID = x.Relationid where QBMRelation.generateChild <> x.generateChild or QBMRelation.generateParent <> x.generateParent END TRY50 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende: truncate table #Relations exec QBM_PSessionContextSet51 'GenProcID', @GenProcID_R return end 52