Back to OIM Explorer

dbo.QBM_PTriggerDisable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.280 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVIntToString source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PIndicatorChange source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_TIQBMGuidReplace source text reference
  • references source dbo.QBM_TUIndicator474488 source text reference

References

Referenced By

Complete Source

SQL131 lines
1CREATE PROCEDURE QBM_PTriggerDisable(2  @TablePattern nvarchar(64) = '%',3  @TriggerPattern nvarchar(64) = '%',4  @UseTriggerPatternForNotLike BIT = 05)6AS7BEGIN8  DECLARE @musterTrigger nvarchar(1024)9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @TabAktuell nvarchar(64)11  DECLARE @TableType varchar(16)12  DECLARE @Triggername nvarchar(64)13  DECLARE @elements QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  DECLARE @JournalMessage nvarchar(1000)17  DECLARE @DebugSwitch int = 018  DECLARE @DebugLevel varchar(1) = 'I'19  DECLARE @MessageType varchar(1) = 'W'20  DECLARE @CountElementsChanged int = 021  SET XACT_ABORT OFF22  BEGIN TRY23    SET nocount24      ON25    SELECT @musterTrigger = N 'alter %type% "%table%" disable trigger %triggername% '26    INSERT INTO @elements(LongIdent1,27    Ident2,28    LongIdent2)29    SELECT30      DISTINCT o.name,31    CASE o.type32      WHEN 'U' THEN33    'Table'34    ELSE ''35    END AS TableType,36    CASE @TriggerPattern37      WHEN '%' THEN38    'all'39    ELSE '"' + t.name + '"'40    END AS Triggername41    FROM sys.tables o42    JOIN sys.triggers t43      ON o.object_id = t.parent_id44    JOIN sys.schemas s45      ON o.schema_id = s.schema_id46    WHERE47      o.type IN('U') AND o.is_memory_optimized = 0 AND o.name LIKE @TablePattern AND s.name = 'dbo' AND t.is_disabled = 0 AND t.name != 'QBM_TUIndicator474488' AND48    (@UseTriggerPatternForNotLike = 0 AND t.name LIKE @TriggerPattern OR @UseTriggerPatternForNotLike = 1 AND t.name NOT LIKE @TriggerPattern) AND49    NOT(@TablePattern = '%' AND @TriggerPattern = '%' AND t.name = 'QBM_TIQBMGuidReplace')50    ORDER BY 151    SELECT @ElementCount = @@ROWCOUNT52    IF @ElementCount > 053    BEGIN54      EXEC QBM_PIndicatorChange 055    END56    SELECT @ElementIndex = 157    WHILE @ElementIndex <= @ElementCount58    BEGIN59      SELECT60        TOP 1 @TabAktuell = bu.LongIdent1,61        @TableType = bu.Ident2,62        @TriggerName = bu.LongIdent263      FROM @elements bu64      WHERE65        bu.ElementIndex = @ElementIndex66      SELECT67        @SQLcmd = replace(replace(replace(@musterTrigger, '%table%', @TabAktuell), '%type%', @TableType),68        '%triggername%',69        @Triggername)70      IF @DebugSwitch > 071      BEGIN72        print @SQLcmd73      END74      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,75        @LockTimeout_ms = 2500,76        @MaxWaitTimeForLock_s = 10,77        @ProcIDForJournal = @@procid,78        @HandleErrorSilent = 0,79        @ExecuteWithTransact = 080      SELECT @CountElementsChanged += 181      SELECT @ElementIndex += 182    END83    IF @CountElementsChanged > 0 AND EXISTS(84      SELECT TOP 1 185      FROM sys.objects o86      WHERE87        o.name = 'QBM_PJournal' AND type = 'P')88    BEGIN89      SELECT90        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),91        '') + '|' + @TablePattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'92      IF NOT EXISTS(93        SELECT TOP 1 194        FROM DialogDatabase95        WHERE96          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(97      SELECT TOP 1 198      FROM sys.tables t99        WITH(readpast)100      JOIN sys.columns c101        WITH(readpast)102        ON t.object_id = c.object_id103      WHERE104        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')105      BEGIN106        EXEC QBM_PJournal @JournalMessage,107          @@procid,108          @MessageType,109          @DebugLevel,110        20111      END112      ELSE113      BEGIN114        EXEC QBM_PJournal @JournalMessage,115          @@procid,116        'I',117        'I'118      END119    END120  END TRY121  BEGIN CATCH122    EXEC QBM_PSessionErrorAdd DEFAULT123    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()124    RAISERROR(@Rethrow,125    18,126    1)127      WITH NOWAIT128  END CATCH129  endLabel:130  RETURN131END
Open raw exported source
SQL ยท Raw22 lines
1   create   procedure QBM_PTriggerDisable (@TablePattern nvarchar(64)= '%' ,@TriggerPattern nvarchar(64) = '%' ,@UseTriggerPatternForNotLike bit2 = 0 ) as begin declare @musterTrigger nvarchar(1024) declare @SQLcmd nvarchar(max) declare @TabAktuell nvarchar(64) declare @TableType varchar(16) declare3 @Triggername nvarchar(64)    declare @elements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @JournalMessage nvarchar(10004) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'I' declare @MessageType varchar(1) = 'W' declare @CountElementsChanged int = 0 SET XACT_ABORT5 OFF BEGIN TRY set nocount on select @musterTrigger = N'alter %type% "%table%" disable trigger %triggername% ' insert into @elements(LongIdent1, Ident26, LongIdent2) select distinct o.name , case o.type when 'U' then 'Table'  else '' end as TableType , case @TriggerPattern when '%' then 'all' else '"' 7+ t.name + '"' end as Triggername from sys.tables o join sys.triggers t on o.object_id = t.parent_id  join sys.schemas s on o.schema_id = s.schema_id where8 o.type in( 'U'  ) and o.is_memory_optimized = 0 and o.name like @TablePattern and s.name = 'dbo' and t.is_disabled = 0 and t.name != 'QBM_TUIndicator474488'9 and ( @UseTriggerPatternForNotLike = 0 and t.name like @TriggerPattern or @UseTriggerPatternForNotLike = 1 and t.name not like @TriggerPattern )  and 10not (@TablePattern = '%' and @TriggerPattern = '%' and t.name = 'QBM_TIQBMGuidReplace' ) order by 1 select @ElementCount = @@ROWCOUNT  if @ElementCount11 > 0 begin exec QBM_PIndicatorChange 0 end select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TabAktuell = bu.LongIdent112 , @TableType = bu.Ident2 , @TriggerName = bu.LongIdent2 from @elements bu where bu.ElementIndex = @ElementIndex select @SQLcmd = replace(replace(replace13(@musterTrigger , '%table%', @TabAktuell) , '%type%', @TableType) , '%triggername%', @Triggername) if @DebugSwitch > 0 begin print @SQLcmd end  exec QBM_PExecuteSQLWithRetry_LLP14 @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10  , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact15 = 0 select @CountElementsChanged += 1 select @ElementIndex += 1 end  if @CountElementsChanged > 0 and exists (select top 1 1 from sys.objects o where 16o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + 17isnull(OBJECT_NAME(@@procid), '') + '|' + @TablePattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'  if not exists (select top 1 1 from18 DialogDatabase where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select top 1 1 from sys.tables t with (readpast) join sys.columns c with19 (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage20, @@procid, @MessageType, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd21 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  return end 22