Back to OIM Explorer

dbo.QBM_PDeleteDeep_L

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-OrgAutoChild / QER_ZITShopProductNodeCheck at line 81; Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 81; Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 85; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 11.362 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 -> QER-K-OrgAutoChild / QER_ZITShopProductNodeCheck at line 81
  • Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 81
  • Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 85
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZITShopProductNodeCheck at line 81 Bulk DBQueue insert -> QER-K-OrgAutoChild / QER_ZITShopProductNodeCheck at line 81
  • queues DBQueue task QER_ZAllForPersonInBasetree at line 81 Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 81
  • queues DBQueue task QER_ZAllForPersonInBasetree at line 85 Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 85
  • references source dbo.QBM_FTParameter1AsInClause source text reference
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDeleteDeep source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PTriggerDisable source text reference
  • references source dbo.QBM_PTriggerEnable source text reference
  • references source dbo.QBM_PTriggersInactiveForTable source text reference
  • queues DBQueue task QER-K-OrgAutoChild -> QER_ZITShopProductNodeCheck QBM_PDBQueueInsert_Bulk 'QER-K-OrgAutoChild', @DBQueueElements_01 end if exists (select top 1 1 from #QBMElementsCollected c where c.ObjectKey like '<Key><T>PersonInITShopOrg</T>%' ) and exists (select top 1 1 from QBMModuleDef d where d.M…
  • queues DBQueue task QER-K-AllForPersonInBaseTree -> QER_ZAllForPersonInBasetree QBM_PDBQueueInsert_Bulk 'QER-K-AllForPersonInBaseTree', @DBQueueElements_02 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow () RAISERROR (@Rethrow, 18, 1) WITH NOWAI…

References

Referenced By

Complete Source

SQL483 lines
1CREATE PROCEDURE QBM_PDeleteDeep_L(2  @ObjectKeysStart QBM_YParameterList READONLY,3  @SingleElementProcess BIT = 0,4  @TestOnly BIT = 05)6AS7BEGIN8  DECLARE @lauf int = 09  DECLARE @DebugSwitch int = 010  DECLARE @SortOrder int11  DECLARE @SQLCmdInsert nvarchar(max)12  DECLARE @cmd nvarchar(max)13  DECLARE @errorDetected BIT = 014  DECLARE @ErrorMessage nvarchar(4000)15  DECLARE @TableName varchar(30)16  DECLARE @Rownumber int17  DECLARE @EventName varchar(30)18  DECLARE @RestmengeVorhanden int = 019  DECLARE @Abarbeiten TABLE(TableName varchar(30) collate database_default,20  rownumber int,21  SortOrder int DEFAULT 0,22  DeepDeleteMethod varchar(16) collate database_default,23  DeepDeleteStatement nvarchar(max) collate database_default)24  DECLARE @GenProcID varchar(38) = newid()25  DECLARE @ElementLast int26  DECLARE @ElementBufferMulti QBM_YCursorBuffer27  DECLARE @ElementCount int28  DECLARE @ElementIndex int29  SET XACT_ABORT OFF30  BEGIN TRY31    SET nocount32      ON drop TABLE33    IF EXISTS #QBMCollectorReferenceElements34    CREATE TABLE #QBMCollectorReferenceElements(35      TableName varchar(30) collate database_default,36      rownumber int identity,37      SortOrder int DEFAULT 0,38      DeepDeleteMethod varchar(16) collate database_default,39      DeepDeleteStatement nvarchar(max) collate database_default40    ) drop TABLE41    IF EXISTS #QBMElementsCollected42    CREATE TABLE #QBMElementsCollected(43      ObjectKey varchar(138) collate database_default,44      DeepDeleteMethod varchar(16) collate database_default,45      ChildColumn varchar(30) collate database_default primary Key(ObjectKey,46      ChildColumn)47    )48    INSERT INTO #QBMCollectorReferenceElements(TableName,49    SortOrder,50    DeepDeleteMethod,51    DeepDeleteStatement52  )53  SELECT54    isnull(b.TableName,55    t.TableName56    ),57    10,58    'Delete',59    'delete ' + isnull(b.TableName,60    t.TableName61    ) + ' where XObjectKey = ''' + s.Parameter1 + ''''62FROM DialogTable t63  WITH(readpast64)65JOIN DialogColumn c166  ON t.UID_DialogTable = c1.UID_DialogTable67JOIN @ObjectKeysStart s68  ON s.Parameter1 LIKE '<Key><T>' + t.TableName + '</T>%' AND c1.ColumnName = 'XObjectKey'69LEFT70OUTER71JOIN DialogTable b72  WITH(readpast73)74  ON t.UID_DialogTableBase = b.UID_DialogTable75INSERT INTO #QBMElementsCollected(ObjectKey,76DeepDeleteMethod,77ChildColumn78)79SELECT80  s.Parameter1,81  'Delete',82  ''83FROM @ObjectKeysStart s84IF @DebugSwitch > 085BEGIN86  print 'startsituation'87  SELECT *88  FROM #QBMCollectorReferenceElements89END90SELECT @Lauf = 191SELECT @SortOrder = 2092WHILE @Lauf > 093BEGIN94  SELECT @Lauf = 095  DELETE @ElementBufferMulti96  INSERT INTO @ElementBufferMulti(ContentFull97)98SELECT99  DISTINCT ' declare @tst QBM_YParameterList100			insert into @tst(Parameter1, Parameter2, Parameter3)101			select distinct  y.XObjectKey , '''102  + rq.DeepDeleteMethod + ''', ''' + rq.ChildColumn + '''103			from ' + rq.ParentTable + ' x join #QBMElementsCollected w on x.XObjectKey = w.Objectkey104						join '105  + rq.ChildTable + ' y on x.' + rq.ParentColumn + ' = y.' + rq.ChildColumn + '106			where Not exists (select top 1 1107								from #QBMElementsCollected e108								where e.Objectkey = y.XObjectKey109								and e.ChildColumn = '''110  + rq.ChildColumn + '''111							)112			and  w.DeepDeleteMethod like ''Delete%''113114		insert into #QBMElementsCollected(ObjectKey, DeepDeleteMethod, ChildColumn)115			select Parameter1, Parameter2, Parameter3116			from @tst117118		insert into #QBMCollectorReferenceElements(TableName, SortOrder, DeepDeleteMethod, DeepDeleteStatement)119		select '''120  + rq.ChildTable + '''  ' + ', %SortOrder% ' + ', ''' + rq.DeepDeleteMethod + ''', ''' + CASE rq.DeepDeleteMethod121  WHEN 'delete' THEN122  'delete ' + rq.ChildTable + ' where XObjectKey in  '' + x.CollectedLine + '' '123  WHEN 'SetNull' THEN124  CONCAT('update ',125  rq.ChildTable,126  ' set ',127  rq.Childcolumn,128  ' = null ',129CASE130  WHEN dbo.QBM_FGIColumnExistsInSchema(rq.Childtable,131'XDateUpdated') = 1 AND rq.ChildColumn NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN132', XDateUpdated = getutcdate(), XUserUpdated =  ''''' + object_name(@@procid) + ''''''133ELSE ''134END,135' where XObjectKey in '' + x.CollectedLine + '' '136)137WHEN 'DeleteX' THEN138CONCAT('update ',139rq.ChildTable,140' set XOrigin = 0 ',141CASE dbo.QBM_FGIColumnExistsInSchema(rq.Childtable,142'XDateUpdated')143  WHEN 1 THEN144', XDateUpdated = getutcdate(), XUserUpdated =  ''''' + object_name(@@procid) + ''''''145ELSE ''146END,147' where XObjectKey in '' + x.CollectedLine + '' ',148nchar(13),149nchar(10),150'delete ',151rq.ChildTable,152' where XObjectKey in '' + x.CollectedLine + ''  '153)154ELSE ''155END + '''156			from ' + CASE @SingleElementProcess157WHEN 0 THEN158' dbo.QBM_FTParameter1AsInClause (@tst) x'159ELSE ' ( select ''('''''' + Parameter1 + '''''')'' as CollectedLine from @tst ) as x'160END161FROM(162SELECT163  r.ChildTable,164  r.ParentTable,165  r.ParentColumn,166  r.ChildColumn,167  r.DeepDeleteMethod168FROM QBM_VForeignKeyRepairable r169JOIN #QBMCollectorReferenceElements w170  ON r.ParenttableBase = w.TableName AND w.DeepDeleteMethod > ' '171JOIN DialogTable t172  WITH(readpast)173  ON r.Childtable = t.TableName AND t.TableType IN('T',174'B')175JOIN DialogColumn cxi176  ON cxi.UID_DialogTable = t.UID_DialogTable AND cxi.ColumnName = 'XObjectKey'177)178AS179rq180SELECT @ElementCount = @@ROWCOUNT181SELECT @ElementIndex = @@IDENTITY - @ElementCount +1182SELECT @ElementLast = @@IDENTITY183WHILE @ElementIndex <= @ElementLast184BEGIN185  SELECT TOP 1 @SQLCmdInsert = bu.ContentFull186  FROM @ElementBufferMulti bu187  WHERE188    bu.ElementIndex = @ElementIndex189  SELECT190    @SQLCmdInsert = REPLACE(@SQLCmdInsert,191    '%SortOrder%',192    str(@SortOrder))193  EXEC sp_executesql @SQLCmdInsert194  SELECT @Lauf += @@ROWCOUNT195  SELECT @SortOrder += 10196  SELECT @ElementIndex += 1197END198END199IF @DebugSwitch > 0200BEGIN201  print 'fertig eingesammelt'202END203IF @DebugSwitch > 0204BEGIN205  print '#QBMElementsCollected'206  SELECT *207  FROM #QBMElementsCollected print '#QBMCollectorReferenceElements'208  SELECT *209  FROM #QBMCollectorReferenceElements210  ORDER BY SortOrder DESC211END212INSERT INTO @Abarbeiten(DeepDeleteMethod,213DeepDeleteStatement,214rownumber,215SortOrder,216TableName)217SELECT218  DISTINCT DeepDeleteMethod,219  DeepDeleteStatement,220  rownumber,221  SortOrder,222  TableName223FROM #QBMCollectorReferenceElements224IF @DebugSwitch > 0 OR @TestOnly = 1225BEGIN226  print 'Elements to process'227  SELECT228    DISTINCT a.TableName,229    a.SortOrder,230    a.DeepDeleteStatement231  FROM @Abarbeiten a232  WHERE233    a.DeepDeleteStatement > ' '234  ORDER BY a.SortOrder DESC235END236truncate TABLE #QBMCollectorReferenceElements237IF @TestOnly = 1238BEGIN239  GOTO endlabel240END241EXEC QBM_PTriggerDisable '%',242'GEN[_]%',243  @UseTriggerPatternForNotLike = 1244BEGIN245  TRANSACTION246  SELECT @Rownumber = -1247  WHILE @Rownumber IS NOT NULL248  BEGIN249    SELECT @Rownumber = NULL250    SELECT251      TOP 1 @Rownumber = e.rownumber,252      @cmd = e.DeepDeleteStatement,253      @SortOrder = e.SortOrder,254      @TableName = e.TableName255    FROM @Abarbeiten e256    ORDER BY e.SortOrder DESC257    IF @DebugSwitch > 0258    BEGIN259      print @cmd260    END261    IF @Rownumber IS NULL262    BEGIN263      CONTINUE264    END265    IF @cmd > ' '266    BEGIN267      IF @DebugSwitch > 0268      BEGIN269        print @cmd270      END271      BEGIN TRY272        EXEC sp_executesql @cmd273      END TRY274      BEGIN CATCH275        SELECT @ErrorMessage = ERROR_MESSAGE()276        EXEC QBM_PSessionErrorAdd DEFAULT,277          @cmd278        SELECT @errorDetected = 1279      END CATCH280    END281    IF @errorDetected = 1282    BEGIN283      IF @DebugSwitch > 0284      BEGIN285        print '##################### ERROR #########' print @ErrorMessage286      END287      IF @ErrorMessage LIKE '%The DELETE statement conflicted with the REFERE%' OR @ErrorMessage LIKE '%Cannot delete%because%does exist%'288      BEGIN289        SELECT TOP 1 @SortOrder = e.SortOrder -5290        FROM @Abarbeiten e291        WHERE292          e.TableName <> @TableName AND e.SortOrder < @SortOrder293        ORDER BY e.SortOrder DESC294        UPDATE @Abarbeiten295        SET SortOrder = @SortOrder296        FROM @Abarbeiten e297        WHERE298          e.rownumber = @Rownumber299        SELECT @errorDetected = 0300        IF @DebugSwitch > 0301        BEGIN302          print 'Reparaturversuch ' + str(@rownumber)303          SELECT *304          FROM @Abarbeiten305          ORDER BY SortOrder DESC306        END307        IF @SortOrder < -100308        BEGIN309          SELECT @RestmengeVorhanden = 1310          SELECT @Rownumber = NULL CONTINUE311        END312      END313      ELSE314      BEGIN315        SELECT @Rownumber = NULL CONTINUE316      END317    END318    ELSE319    BEGIN320      DELETE @Abarbeiten321      WHERE322        rownumber = @Rownumber323    END324  END325  IF @errorDetected = 1326  BEGIN327    ROLLBACK TRANSACTION328    EXEC QBM_PTriggerEnable '%',329    'GEN[_]%',330      @UseTriggerPatternForNotLike = 1331    RAISERROR('',332    18,333    1)334      WITH NOWAIT335  END336  ELSE337  BEGIN338    COMMIT TRANSACTION339  END340  IF @RestmengeVorhanden > 0341  BEGIN342    print 'not fully deleted, cycles in data dependencies' print 'statemants to execute left are:'343    SELECT344      a.TableName,345      a.DeepDeleteStatement346    FROM @abarbeiten a347    WHERE348      a.DeepDeleteStatement > ' '349  END350  ELSE351  BEGIN352    print 'requested data fully deleted'353  END354  IF @DebugSwitch > 0355  BEGIN356    print 'Nachlese'357    SELECT358      DISTINCT isnull(b.TableName,359      t.TableName),360      v.EventName361    FROM #QBMElementsCollected r362    JOIN(363    VALUES('SetNull', 'update'),364    ('DeleteX', 'Update'),365    ('DeleteX', 'Delete'),366    ('Delete', 'Delete'),367    ('DeleteA', 'Delete')) AS v(DeepDeleteMethod,368    EventName)369      ON r.DeepDeleteMethod = v.DeepDeleteMethod370    JOIN DialogTable t371      WITH(readpast)372      ON r.ObjectKey LIKE '<Key><T>' + t.TableName + '</T>%'373    LEFT374    OUTER375    JOIN DialogTable b376      WITH(readpast)377      ON t.UID_DialogTableBase = b.UID_DialogTable378  END379  DELETE @ElementBufferMulti380  INSERT INTO @ElementBufferMulti(Ident1,381  Ident2)382  SELECT383    DISTINCT isnull(b.TableName,384    t.TableName),385    v.EventName386  FROM #QBMElementsCollected r387  JOIN(388  VALUES('SetNull', 'update'),389  ('DeleteX', 'Update'),390  ('DeleteX', 'Delete'),391  ('Delete', 'Delete'),392  ('DeleteA', 'Delete')) AS v(DeepDeleteMethod,393  EventName)394    ON r.DeepDeleteMethod = v.DeepDeleteMethod395  JOIN DialogTable t396    WITH(readpast)397    ON r.ObjectKey LIKE '<Key><T>' + t.TableName + '</T>%'398  LEFT399  OUTER400  JOIN DialogTable b401    WITH(readpast)402    ON t.UID_DialogTableBase = b.UID_DialogTable403  SELECT @ElementCount = @@ROWCOUNT404  SELECT @ElementIndex = @@IDENTITY - @ElementCount +1405  SELECT @ElementLast = @@IDENTITY406  WHILE @ElementIndex <= @ElementLast407  BEGIN408    SELECT409      TOP 1 @tableName = bu.Ident1,410      @EventName = bu.Ident2411    FROM @ElementBufferMulti bu412    WHERE413      bu.ElementIndex = @ElementIndex414    EXEC QBM_PTriggersInactiveForTable @tableName,415      @EventName416    SELECT @ElementIndex += 1417  END418  EXEC QBM_PTriggerEnable '%',419  '%'420  IF EXISTS(421    SELECT TOP 1 1422    FROM #QBMElementsCollected c423    WHERE424      c.ObjectKey LIKE '<Key><T>%ITShopOrg%</T>%') AND dbo.QBM_FGIModuleExists('QER') = 1425  BEGIN426    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw427    INSERT INTO @DBQueueElements_01(object,428    subobject,429    genprocid)430    SELECT431      x.uid,432      NULL,433      @GenProcID434    FROM(435    SELECT b.UID_Org AS uid436    FROM BaseTree b437    WHERE438      b.ITShopInfo = 'BO') AS x439    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-OrgAutoChild',440      @DBQueueElements_01441  END442  IF EXISTS(443    SELECT TOP 1 1444    FROM #QBMElementsCollected c445    WHERE446      c.ObjectKey LIKE '<Key><T>PersonInITShopOrg</T>%') AND EXISTS(447  SELECT TOP 1 1448  FROM QBMModuleDef d449  WHERE450    d.ModuleName = 'QER')451  BEGIN452    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw453    INSERT INTO @DBQueueElements_02(object,454    subobject,455    genprocid)456    SELECT457      x.uid_person,458      x.uid_org,459      @GenProcID460    FROM(461    SELECT462      dbo.QBM_FCVObjectkeyToElement('columnvalue1', c.ObjectKey) AS uid_org,463      dbo.QBM_FCVObjectkeyToElement('columnvalue2', c.ObjectKey) AS uid_person464    FROM #QBMElementsCollected c465    WHERE466      c.ObjectKey LIKE '<Key><T>PersonInITShopOrg</T>%') AS x467    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForPersonInBaseTree',468      @DBQueueElements_02469  END470END TRY471BEGIN CATCH472  EXEC QBM_PSessionErrorAdd DEFAULT473  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()474  RAISERROR(@Rethrow,475  18,476  1)477    WITH NOWAIT478END CATCH479ende:480EXEC QBM_PTriggerEnable '%',481'%' endLabel: truncate TABLE #QBMCollectorReferenceElements truncate TABLE #QBMElementsCollected482RETURN483END
Open raw exported source
SQL · Raw89 lines
1 create   procedure QBM_PDeleteDeep_L (@ObjectKeysStart QBM_YParameterList readonly , @SingleElementProcess bit = 0   , @TestOnly bit = 0  ) as 2begin declare @lauf int = 0 declare @DebugSwitch int = 0 declare @SortOrder int declare @SQLCmdInsert nvarchar(max) declare @cmd nvarchar(max) declare 3@errorDetected bit = 0 declare @ErrorMessage nvarchar(4000) declare @TableName varchar(30) declare @Rownumber int declare @EventName varchar(30) declare4 @RestmengeVorhanden int = 0 declare @Abarbeiten table (TableName varchar(30) collate database_default , rownumber int , SortOrder int default 0 , DeepDeleteMethod5 varchar(16) collate database_default  , DeepDeleteStatement nvarchar(max) collate database_default ) declare @GenProcID varchar(38) = newid() declare 6@ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount7 on drop table if exists #QBMCollectorReferenceElements create table #QBMCollectorReferenceElements (TableName varchar(30) collate database_default , rownumber8 int identity , SortOrder int default 0 , DeepDeleteMethod varchar(16) collate database_default  , DeepDeleteStatement nvarchar(max) collate database_default9 ) drop table if exists #QBMElementsCollected create table #QBMElementsCollected (ObjectKey varchar(138) collate database_default , DeepDeleteMethod varchar10(16) collate database_default  , ChildColumn varchar(30) collate database_default  primary Key (ObjectKey,ChildColumn) ) insert into #QBMCollectorReferenceElements11(TableName, SortOrder, DeepDeleteMethod, DeepDeleteStatement) select isnull(b.TableName, t.TableName), 10, 'Delete' , 'delete ' + isnull(b.TableName, t.TableName12) + ' where XObjectKey = ''' + s.Parameter1 + '''' from DialogTable t with (readpast) join DialogColumn c1 on t.UID_DialogTable = c1.UID_DialogTable join13 @ObjectKeysStart s on s.Parameter1 like '<Key><T>' + t.TableName + '</T>%' and c1.ColumnName = 'XObjectKey' left outer join DialogTable b with (readpast14) on t.UID_DialogTableBase = b.UID_DialogTable  insert into #QBMElementsCollected(ObjectKey, DeepDeleteMethod, ChildColumn)  select s.Parameter1, 'Delete'15, '' from @ObjectKeysStart s if @DebugSwitch > 0 begin print 'startsituation' select * from #QBMCollectorReferenceElements end select @Lauf = 1 select 16@SortOrder = 20 while @Lauf > 0 begin select @Lauf = 0 delete  @ElementBufferMulti insert into @ElementBufferMulti (ContentFull) select distinct ' declare @tst QBM_YParameterList17			insert into @tst(Parameter1, Parameter2, Parameter3)18			select distinct  y.XObjectKey , '''19 + rq.DeepDeleteMethod + ''', ''' + rq.ChildColumn + '''20			from ' + rq.ParentTable + ' x join #QBMElementsCollected w on x.XObjectKey = w.Objectkey21						join '22 + rq.ChildTable + ' y on x.' + rq.ParentColumn + ' = y.' + rq.ChildColumn + '23			where Not exists (select top 1 124								from #QBMElementsCollected e25								where e.Objectkey = y.XObjectKey26								and e.ChildColumn = '''27 + rq.ChildColumn + '''28							)29			and  w.DeepDeleteMethod like ''Delete%''3031		insert into #QBMElementsCollected(ObjectKey, DeepDeleteMethod, ChildColumn)32			select Parameter1, Parameter2, Parameter333			from @tst3435		insert into #QBMCollectorReferenceElements(TableName, SortOrder, DeepDeleteMethod, DeepDeleteStatement)36		select '''37 + rq.ChildTable + '''  ' + ', %SortOrder% ' + ', ''' + rq.DeepDeleteMethod + ''', ''' + case rq.DeepDeleteMethod when 'delete' then 'delete ' + rq.ChildTable38 + ' where XObjectKey in  '' + x.CollectedLine + '' ' when 'SetNull' then concat('update ' , rq.ChildTable , ' set ' , rq.Childcolumn , ' = null ' , case39 when dbo.QBM_FGIColumnExistsInSchema(rq.Childtable, 'XDateUpdated') = 1 and rq.ChildColumn not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  '''''40 + object_name(@@procid) + '''''' else '' end ,' where XObjectKey in '' + x.CollectedLine + '' ' ) when 'DeleteX' then concat('update ' , rq.ChildTable41 , ' set XOrigin = 0 ' , case dbo.QBM_FGIColumnExistsInSchema(rq.Childtable, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated =  '''''42 + object_name(@@procid) + '''''' else '' end , ' where XObjectKey in '' + x.CollectedLine + '' ' , nchar(13) , nchar(10) , 'delete ' , rq.ChildTable ,43 ' where XObjectKey in '' + x.CollectedLine + ''  ' ) else '' end + '''44			from ' + case @SingleElementProcess when 0 then ' dbo.QBM_FTParameter1AsInClause (@tst) x'45 else ' ( select ''('''''' + Parameter1 + '''''')'' as CollectedLine from @tst ) as x' end from ( select r.ChildTable, r.ParentTable, r.ParentColumn, r.ChildColumn46 , r.DeepDeleteMethod from QBM_VForeignKeyRepairable r join #QBMCollectorReferenceElements w on r.ParenttableBase = w.TableName  and w.DeepDeleteMethod47 > ' ' join DialogTable t with (readpast) on r.Childtable = t.TableName and t.TableType in ('T', 'B')  join DialogColumn cxi on cxi.UID_DialogTable = t.UID_DialogTable48 and cxi.ColumnName = 'XObjectKey'  ) as rq  select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast49 = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @SQLCmdInsert = bu.ContentFull from @ElementBufferMulti bu where bu.ElementIndex =50 @ElementIndex select @SQLCmdInsert = REPLACE(@SQLCmdInsert, '%SortOrder%', str(@SortOrder))     exec sp_executesql @SQLCmdInsert select @Lauf += @@ROWCOUNT51 select @SortOrder += 10 select @ElementIndex += 1 end      end  if @DebugSwitch > 0 begin print 'fertig eingesammelt' end if @DebugSwitch > 0 begin print52 '#QBMElementsCollected' select * from #QBMElementsCollected print '#QBMCollectorReferenceElements' select * from #QBMCollectorReferenceElements order 53by SortOrder desc end insert into @Abarbeiten (DeepDeleteMethod, DeepDeleteStatement, rownumber, SortOrder, TableName) select distinct DeepDeleteMethod54, DeepDeleteStatement, rownumber, SortOrder, TableName from #QBMCollectorReferenceElements if @DebugSwitch > 0 or @TestOnly = 1 begin print 'Elements to process'55 select distinct a.TableName, a.SortOrder , a.DeepDeleteStatement from @Abarbeiten a where a.DeepDeleteStatement > ' ' order by a.SortOrder desc end truncate56 table #QBMCollectorReferenceElements   if @TestOnly = 1 begin goto endlabel end exec QBM_PTriggerDisable '%', 'GEN[_]%', @UseTriggerPatternForNotLike 57= 1 begin transaction select @Rownumber = -1 while @Rownumber is not null begin select @Rownumber = null select top 1 @Rownumber = e.rownumber , @cmd =58 e.DeepDeleteStatement , @SortOrder = e.SortOrder , @TableName = e.TableName from @Abarbeiten e order by e.SortOrder desc  if @DebugSwitch > 0 begin print59 @cmd end if @Rownumber is null begin continue end if @cmd > ' ' begin if @DebugSwitch > 0 begin print @cmd end begin try exec sp_executesql @cmd end try60 begin Catch  select @ErrorMessage = ERROR_MESSAGE() exec QBM_PSessionErrorAdd default, @cmd select @errorDetected = 1 end catch end if @errorDetected 61= 1 begin if @DebugSwitch > 0 begin print '##################### ERROR #########' print @ErrorMessage end if @ErrorMessage like '%The DELETE statement conflicted with the REFERE%'62 or @ErrorMessage like '%Cannot delete%because%does exist%' begin   select top 1 @SortOrder = e.SortOrder -5 from @Abarbeiten e where e.TableName <> @TableName63 and e.SortOrder < @SortOrder order by e.SortOrder desc update @Abarbeiten set SortOrder = @SortOrder from @Abarbeiten e where e.rownumber = @Rownumber64 select @errorDetected = 0 if @DebugSwitch > 0 begin print 'Reparaturversuch ' + str(@rownumber) select * from @Abarbeiten order by SortOrder desc end 65if @SortOrder < -100 begin  select @RestmengeVorhanden = 1 select @Rownumber = null continue end end else begin  select @Rownumber = null continue end 66end else begin delete  @Abarbeiten where rownumber = @Rownumber end end  if @errorDetected = 1 begin rollback transaction  exec QBM_PTriggerEnable '%',67 'GEN[_]%', @UseTriggerPatternForNotLike = 1 RAISERROR ('', 18, 1) WITH NOWAIT end else begin commit transaction end if @RestmengeVorhanden > 0 begin print68 'not fully deleted, cycles in data dependencies' print 'statemants to execute left are:' select a.TableName, a.DeepDeleteStatement from @abarbeiten a 69where a.DeepDeleteStatement > ' ' end else begin print 'requested data fully deleted' end if @DebugSwitch > 0 begin print 'Nachlese' select distinct isnull70(b.TableName, t.TableName), v.EventName from #QBMElementsCollected r join ( values ( 'SetNull' , 'update') , ( 'DeleteX', 'Update') , ( 'DeleteX', 'Delete'71) , ('Delete', 'Delete') , ('DeleteA', 'Delete') ) as v (DeepDeleteMethod, EventName) on r.DeepDeleteMethod = v.DeepDeleteMethod join DialogTable t with72 (readpast) on r.ObjectKey like '<Key><T>' + t.TableName + '</T>%' left outer join DialogTable b with (readpast) on t.UID_DialogTableBase = b.UID_DialogTable73 end  delete  @ElementBufferMulti insert into @ElementBufferMulti (Ident1, Ident2) select distinct isnull(b.TableName, t.TableName), v.EventName from #QBMElementsCollected74 r join ( values ( 'SetNull', 'update') , ( 'DeleteX', 'Update') , ( 'DeleteX', 'Delete') , ( 'Delete', 'Delete') , ( 'DeleteA', 'Delete') ) as v (DeepDeleteMethod75, EventName) on r.DeepDeleteMethod = v.DeepDeleteMethod join DialogTable t with (readpast) on r.ObjectKey like '<Key><T>' + t.TableName + '</T>%' left 76outer join DialogTable b with (readpast) on t.UID_DialogTableBase = b.UID_DialogTable select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY77 - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @tableName = bu.Ident1 , @EventName = bu.Ident278 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex exec QBM_PTriggersInactiveForTable @tableName, @EventName select @ElementIndex += 1 79end   exec QBM_PTriggerEnable '%', '%' if exists (select top 1 1 from #QBMElementsCollected c where c.ObjectKey like '<Key><T>%ITShopOrg%</T>%' ) and dbo.QBM_FGIModuleExists80('QER') = 1 begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID81 from ( select b.UID_Org as uid from BaseTree b where b.ITShopInfo = 'BO' ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-OrgAutoChild', @DBQueueElements_0182 end if exists (select top 1 1 from #QBMElementsCollected c where c.ObjectKey like '<Key><T>PersonInITShopOrg</T>%' ) and exists (select top 1 1 from QBMModuleDef83 d where d.ModuleName = 'QER' ) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select84 x.uid_person, x.uid_org, @GenProcID from ( select dbo.QBM_FCVObjectkeyToElement('columnvalue1', c.ObjectKey) as uid_org , dbo.QBM_FCVObjectkeyToElement85('columnvalue2', c.ObjectKey) as uid_person from #QBMElementsCollected c where c.ObjectKey like '<Key><T>PersonInITShopOrg</T>%' ) as x exec QBM_PDBQueueInsert_Bulk86 'QER-K-AllForPersonInBaseTree', @DBQueueElements_02 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow87() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: exec QBM_PTriggerEnable '%', '%' endLabel: truncate table #QBMCollectorReferenceElements truncate88 table #QBMElementsCollected  return end 89