Back to OIM Explorer

dbo.QBM_TUDialogCountry

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on DialogCountry. Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 25; Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 25; Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 29; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 3.056 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-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 25
  • Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 25
  • Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 29
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: DialogCountry

Typed Edges

  • queues DBQueue task QBM_ZCountryUTCOffset at line 25 Bulk DBQueue insert -> QBM-K-CommonCountryUTCOffset / QBM_ZCountryUTCOffset at line 25
  • queues DBQueue task QBM_ZStateUTCOffset2 at line 25 Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 25
  • queues DBQueue task QBM_ZStateUTCOffset2 at line 29 Bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 29
  • trigger on table DialogCountry Trigger parent table: DialogCountry
  • 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

SQL145 lines
1CREATE trigger QBM_TUDialogCountry2  ON DialogCountry 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 DialogCountry x26          ON d.UID_DialogCountry = x.UID_DialogCountry27        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_DialogCountry38      FROM deleted d39      JOIN DialogCountry x40        ON d.UID_DialogCountry = x.UID_DialogCountry41      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 = 'DialogCountry' AND t.TableType IN('B',73    'T'74  ) AND r.IsMNRelation = 0 AND t.IsMAllTable = 0 AND r.ChildTable NOT IN('UCIUser',75  'DialogState',76  'DialogCountryHoliday'77)78IF @DebugSwitch > 079BEGIN80  print @SQLCmd81END82EXEC sp_executesql @SQLCmd83END84END85IF86UPDATE(IsInUse87)88BEGIN89  DECLARE @DBQueueElements_01 QBM_YDBQueueRaw90  INSERT INTO @DBQueueElements_01(Object,91  SubObject,92  GenProcID93)94SELECT95  x.uid,96  NULL,97  @GenProcID98FROM(99SELECT i.UID_DialogCountry100AS101uid102FROM DialogCountry i103JOIN deleted d104  ON i.UID_DialogCountry = d.UID_DialogCountry105WHERE106  isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0)) AS x107EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonCountryUTCOffset',108  @DBQueueElements_01109END110IF111UPDATE(AvgUTCOffset) OR112UPDATE(IsDayLightSaving)113BEGIN114  DECLARE @DBQueueElements_02 QBM_YDBQueueRaw115  INSERT INTO @DBQueueElements_02(Object,116  SubObject,117  GenProcID)118  SELECT119    x.uid,120    NULL,121    @GenProcID122  FROM(123  SELECT s.uid_DialogState AS uid124  FROM DialogCountry i125  JOIN deleted d126    ON i.UID_DialogCountry = d.UID_DialogCountry127  JOIN DialogState s128    ON s.UID_DialogCountry = i.UID_DialogCountry129  WHERE130    isnull(i.AvgUTCOffset, 0) <> isnull(d.AvgUTCOffset, 0) OR isnull(i.IsDayLightSaving, 0) <> isnull(d.IsDayLightSaving,131  0)) AS x132  EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset2',133    @DBQueueElements_02134END135END TRY136BEGIN CATCH137  EXEC QBM_PSessionErrorAdd DEFAULT138  RAISERROR('',139  18,140  1)141    WITH NOWAIT142END CATCH143EndLabel:144RETURN145END
Open raw exported source
SQL ยท Raw31 lines
1 create   trigger QBM_TUDialogCountry on DialogCountry  for Update not for Replication as begin declare @SQLCmd nvarchar(max) declare @DebugSwitch2 int = 0 declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select3 top 1 1 from deleted) goto start return start:  if UPDATE(IsInUse) begin if exists (select top 1 1 from deleted d join DialogCountry x on d.UID_DialogCountry4 = x.UID_DialogCountry where d.IsInUse = 1 and x.IsInUse = 0 ) begin   drop table if exists #Verwaltungseinheit create table #Verwaltungseinheit(UID_PK5 varchar(38) collate database_default)  insert into #Verwaltungseinheit(UID_PK) select d.UID_DialogCountry from deleted d join DialogCountry x on d.UID_DialogCountry6 = x.UID_DialogCountry 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 = 'DialogCountry' and22 t.TableType in ('B', 'T') and r.IsMNRelation = 0 and t.IsMAllTable = 0  and r.ChildTable not in ('UCIUser'  , 'DialogState', 'DialogCountryHoliday' ) 23 if @DebugSwitch > 0 begin print @SQLCmd end exec sp_executesql @SQLCmd end  end   if UPDATE(IsInUse) begin declare @DBQueueElements_01 QBM_YDBQueueRaw24 insert into @DBQueueElements_01 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select i.UID_DialogCountry as uid from DialogCountry25 i join deleted d on i.UID_DialogCountry = d.UID_DialogCountry where isnull(i.IsInUse, 0) <> isnull(d.IsInUse, 0) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonCountryUTCOffset'26, @DBQueueElements_01 end  if UPDATE(AvgUTCOffset) or UPDATE(IsDayLightSaving) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_0227 (Object, SubObject, GenProcID) select x.uid, null, @GenProcID from (select s.uid_DialogState as uid from DialogCountry i join deleted d on i.UID_DialogCountry28 = d.UID_DialogCountry join DialogState s on s.UID_DialogCountry = i.UID_DialogCountry where isnull(i.AvgUTCOffset, 0) <> isnull(d.AvgUTCOffset, 0) or 29isnull(i.IsDayLightSaving, 0) <> isnull(d.IsDayLightSaving, 0) ) as x exec QBM_PDBQueueInsert_Bulk 'QBM-K-CommonStateUTCOffset2', @DBQueueElements_02 end30  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH EndLabel:  return end 31