Back to OIM Explorer

dbo.QBM_TUDialogState

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on DialogState. Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 26; Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset / QBM_ZStateUTCOffset at line 26; Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 26; Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 31

Source: sandbox-db sys.sql_modules

Source size: 3.350 characters

Interpretation

  • Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 26
  • Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset / QBM_ZStateUTCOffset at line 26
  • Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 26
  • Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 31
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: DialogState

Typed Edges

  • queues DBQueue task QBM_ZStateUTCOffset2 at line 26 Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 26
  • queues DBQueue task QBM_ZStateUTCOffset at line 26 Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset / QBM_ZStateUTCOffset at line 26
  • queues DBQueue task QBM_ZCountryUTCOffset at line 26 Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 26
  • queues DBQueue task QBM_ZCountryUTCOffset at line 31 Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 31
  • trigger on table DialogState Trigger parent table: DialogState
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL155 lines
1CREATE trigger QBM_TUDialogState2  ON DialogState FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @SQLCmd nvarchar(max)7  DECLARE @DebugSwitch int = 08  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')9  BEGIN TRY10    IF EXISTS(11      SELECT TOP 1 112      FROM inserted)13    GOTO start14    IF EXISTS(15      SELECT TOP 1 116      FROM deleted)17    GOTO start18    RETURN start:19    IF20    UPDATE(IsInUse)21    BEGIN22      IF EXISTS(23        SELECT TOP 1 124        FROM deleted d25        JOIN DialogState x26          ON d.UID_DialogState = x.UID_DialogState27        WHERE28          d.IsInUse = 1 AND x.IsInUse = 0)29      BEGIN30        drop TABLE31        IF EXISTS #Verwaltungseinheit32        CREATE TABLE #Verwaltungseinheit(33          UID_PK varchar(38) collate database_default34        )35        INSERT INTO #Verwaltungseinheit(UID_PK36      )37      SELECT d.UID_DialogState38      FROM deleted d39      JOIN DialogState x40        ON d.UID_DialogState = x.UID_DialogState41      WHERE42        d.IsInUse = 1 AND x.IsInUse = 043      SELECT44        TOP 1 @SQLCmd = CONCAT('declare @ElemKey varchar(138) = null45					declare @Message nvarchar(1000)46					',47        string_agg(convert(nvarchar(max),48        CONCAT(N '49					select @ElemKey = null50					select top 1 @ElemKey = x.XObjectKey51					from ',52        r.ChildTable,53        ' x join #Verwaltungseinheit y on x.',54        r.ChildColumn,55        ' = y.UID_PK56					if @ElemKey > '' ''57					 begin58						select @Message = CONCAT( ''#LDS#Changes cannot take place because assignments still exist. Table: {0} Element: {1}.|'' , '''59        ,60        r.ChildTable,61        ''' , ''|'' , @ElemKey , ''|'' )62						raiserror( @message, 18, 2) with nowait63					 end64					 ')),65        convert(nvarchar(10),66        NCHAR(13) + nchar(10)))67        )68    FROM QBM_VQBMRelation r69    JOIN DialogTable t70      ON r.UID_DialogTableChild = t.UID_DialogTable71    WHERE72      r.ParentTable = 'DialogState' AND t.TableType IN('B',73    'T'74  ) AND r.IsMNRelation = 0 AND t.IsMAllTable = 0 AND r.ChildTable NOT IN('UCIUser',75  'DialogStateHoliday'76)77IF @DebugSwitch > 078BEGIN79  print @SQLCmd80END81EXEC sp_executesql @SQLCmd82END83END84IF85UPDATE(IsInUse86) OR87UPDATE(UID_DialogCountry88)89BEGIN90  DECLARE @DBQueueElements_01 QBM_YDBQueueRaw91  INSERT INTO @DBQueueElements_01(Object,92  SubObject,93  GenProcID94)95SELECT96  x.uid,97  NULL,98  @GenProcID99FROM(100SELECT i.UID_DialogState101AS102uid103FROM DialogState i104JOIN deleted d105  ON i.UID_DialogState = d.UID_DialogState106WHERE107  isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) OR isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry,108'')) AS x109EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset2',110  @DBQueueElements_01111EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset',112  @DBQueueElements_01113END114IF115UPDATE(IsInUse) OR116UPDATE(UID_DialogCountry)117BEGIN118  DECLARE @DBQueueElements_02 QBM_YDBQueueRaw119  INSERT INTO @DBQueueElements_02(Object,120  SubObject,121  GenProcID)122  SELECT123    x.uid,124    NULL,125    @GenProcID126  FROM(127  SELECT i.UID_DialogCountry AS uid128  FROM DialogState i129  JOIN deleted d130    ON i.UID_DialogState = d.UID_DialogState131  WHERE132    isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) OR isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry,133  '')134  UNION135  SELECT d.UID_DialogCountry AS uid136  FROM DialogState i137  JOIN deleted d138    ON i.UID_DialogState = d.UID_DialogState139  WHERE140    isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) OR isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry,141  '')) AS x142  EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonCountryUTCOffset',143    @DBQueueElements_02144END145END TRY146BEGIN CATCH147  EXEC QBM_PSessionErrorAdd DEFAULT148  RAISERROR('',149  18,150  1)151    WITH NOWAIT152END CATCH153EndLabel:154RETURN155END
Open raw exported source
SQL ยท Raw33 lines
1create   trigger QBM_TUDialogState on DialogState  for Update not for Replication as begin  declare @SQLCmd nvarchar(max) declare @DebugSwitch int2 = 0 declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select top3 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 varchar5(38) collate database_default)  insert into #Verwaltungseinheit(UID_PK) select d.UID_DialogState from deleted d join DialogState x on d.UID_DialogState6 = x.UID_DialogState where d.IsInUse = 1 and x.IsInUse = 0  select top 1 @SQLCmd = CONCAT( 'declare @ElemKey varchar(138) = null7					declare @Message nvarchar(1000)8					'9 , string_agg( convert(nvarchar(max), CONCAT(N'10					select @ElemKey = null11					select top 1 @ElemKey = x.XObjectKey12					from ', r.ChildTable , ' x join #Verwaltungseinheit y on x.'13, r.ChildColumn ,' = y.UID_PK14					if @ElemKey > '' ''15					 begin16						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 nowait19					 end20					 ' ))  , convert(nvarchar(10), NCHAR(1321) + nchar(10)) )  )  from QBM_VQBMRelation r join DialogTable t on r.UID_DialogTableChild = t.UID_DialogTable where r.ParentTable = 'DialogState' and t.TableType22 in ('B', 'T') and r.IsMNRelation = 0 and t.IsMAllTable = 0  and r.ChildTable not in ('UCIUser'  , 'DialogStateHoliday' )  if @DebugSwitch > 0 begin print23 @SQLCmd end exec sp_executesql @SQLCmd end  end   if UPDATE(IsInUse) or UPDATE(UID_DialogCountry) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert24 into @DBQueueElements_01 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogState as uid from DialogState i join deleted25 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_DialogCountry26, '') ) 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_0228 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogCountry as uid from DialogState i join deleted d on i.UID_DialogState29 = d.UID_DialogState where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) or isnull(i.UID_DialogCountry, '') <> isnull(d.UID_DialogCountry, '') union select30 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_0232 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel:   return end 33