Back to OIM Explorer

dbo.QBM_PTriggerEnable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.026 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_PSessionErrorClean source text reference
  • references source dbo.QBM_TUIndicator474488 source text reference

References

Referenced By

Complete Source

SQL179 lines
1CREATE PROCEDURE QBM_PTriggerEnable(2  @TablePattern nvarchar(64) = '%',3  @TriggerPattern nvarchar(64) = '%',4  @UseTriggerPatternForNotLike BIT = 0,5  @HandleErrorSilent BIT = 0,6  @force BIT = 07)8AS9BEGIN10  DECLARE @musterTrigger nvarchar(1024)11  DECLARE @SQLcmd nvarchar(max)12  DECLARE @TabAktuell varchar(30)13  DECLARE @TableType varchar(16)14  DECLARE @Triggername nvarchar(64)15  DECLARE @elements QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @JournalMessage nvarchar(1000)19  DECLARE @DebugSwitch int = 020  DECLARE @DebugLevel varchar(1) = 'I'21  DECLARE @MessageType varchar(1) = 'I'22  DECLARE @WarFehlerhaft int = 023  DECLARE @CountElementsChanged int = 024  DECLARE @ErrorBuffer QBM_YSessionError25  SET XACT_ABORT OFF26  BEGIN TRY27    SET nocount28      ON29    IF EXISTS(30      SELECT TOP 1 131      FROM DialogDatabase d32      WHERE33        d.UpdatePhase NOT IN(0)) AND @force = 034    BEGIN35      GOTO endLabel36    END37    SELECT @musterTrigger = N 'alter %type% "%table%" enable trigger %triggername% '38    INSERT INTO @elements(LongIdent1,39    Ident2,40    LongIdent2)41    SELECT42      DISTINCT o.name,43    CASE o.type44      WHEN 'U' THEN45    'Table'46    ELSE ''47    END AS TableType,48    CASE @TriggerPattern49      WHEN '%' THEN50    'all'51    ELSE '"' + t.name + '"'52    END AS Triggername53    FROM sys.tables o54    JOIN sys.triggers t55      ON o.object_id = t.parent_id56    WHERE57      t.name != 'QBM_TUIndicator474488' AND o.type IN('U') AND o.is_memory_optimized = 0 AND o.name LIKE @TablePattern AND t.is_disabled = 1 AND58    (@UseTriggerPatternForNotLike = 0 AND t.name LIKE @TriggerPattern OR @UseTriggerPatternForNotLike = 1 AND t.name NOT LIKE @TriggerPattern)59    ORDER BY 160    SELECT @ElementCount = @@ROWCOUNT61    SELECT @ElementIndex = 162    WHILE @ElementIndex <= @ElementCount AND @WarFehlerhaft = 063    BEGIN64      SELECT65        TOP 1 @TabAktuell = bu.LongIdent1,66        @TableType = bu.Ident2,67        @TriggerName = bu.LongIdent268      FROM @elements bu69      WHERE70        bu.ElementIndex = @ElementIndex71      SELECT72        @SQLcmd = replace(replace(replace(@musterTrigger, '%table%', @TabAktuell), '%type%', @TableType),73        '%triggername%',74        @Triggername)75      IF @DebugSwitch > 076      BEGIN77        print @SQLcmd78      END79      BEGIN TRY80        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,81          @LockTimeout_ms = 2500,82          @MaxWaitTimeForLock_s = 10,83          @ProcIDForJournal = @@procid,84          @HandleErrorSilent = 0,85          @ExecuteWithTransact = 086        SELECT @CountElementsChanged += 187        EXEC QBM_PSessionErrorClean88      END TRY89      BEGIN CATCH90        IF @HandleErrorSilent = 091        BEGIN92          DELETE @ErrorBuffer93          INSERT INTO @ErrorBuffer(ErrorMessage,94          ErrorSeverity,95          ErrorState,96          ErrorNumber,97          ProcedureName,98          ProcedureLine,99          MessageDate,100          GenProcID,101          RepeatCounter,102          IsReThrow,103          SourceCode)104          SELECT105            CONCAT('#LDS#Possible invalid foreign keys, run consistency checks: {0}|',106            ERROR_MESSAGE(),107            '|'),108            18,109            1,110            50000,111            object_name(@@PROCID),112            ERROR_LINE(),113            GETUTCDATE(),114            NULL,115            0,116            0,117            @SQLcmd118          EXEC QBM_PSessionErrorAdd @ErrorBuffer119          EXEC QBM_PJournal 'SessionError',120            @@procid,121          'E',122            @DebugLevel,123          20124          EXEC QBM_PSessionErrorClean125        END126        SELECT @WarFehlerhaft = 1127      END CATCH128      SELECT @ElementIndex += 1129    END130    IF @CountElementsChanged > 0 AND EXISTS(131      SELECT TOP 1 1132      FROM sys.objects o133      WHERE134        o.name = 'QBM_PJournal' AND type = 'P')135    BEGIN136      SELECT137        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),138        '') + '|' + @TablePattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'139      IF NOT EXISTS(140        SELECT TOP 1 1141        FROM DialogDatabase142        WHERE143          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(144      SELECT TOP 1 1145      FROM sys.tables t146        WITH(readpast)147      JOIN sys.columns c148        WITH(readpast)149        ON t.object_id = c.object_id150      WHERE151        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')152      BEGIN153        EXEC QBM_PJournal @JournalMessage,154          @@procid,155          @MessageType,156          @DebugLevel,157        20158      END159      ELSE160      BEGIN161        EXEC QBM_PJournal @JournalMessage,162          @@procid,163        'I',164        'I'165      END166    END167    EXEC QBM_PIndicatorChange 1168  END TRY169  BEGIN CATCH170    EXEC QBM_PSessionErrorAdd DEFAULT171    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()172    RAISERROR(@Rethrow,173    18,174    1)175      WITH NOWAIT176  END CATCH177  endLabel:178  RETURN(@WarFehlerhaft)179END
Open raw exported source
SQL ยท Raw27 lines
1   create   procedure QBM_PTriggerEnable (@TablePattern nvarchar(64)= '%' ,@TriggerPattern nvarchar(64) = '%' ,@UseTriggerPatternForNotLike bit 2= 0 ,@HandleErrorSilent bit = 0   , @force bit = 0  ) as begin declare @musterTrigger nvarchar(1024) declare @SQLcmd nvarchar(max) declare @TabAktuell 3varchar(30) declare @TableType varchar(16) declare @Triggername nvarchar(64)     declare @elements QBM_YCursorBuffer declare @ElementCount int declare 4@ElementIndex int declare @JournalMessage nvarchar(1000) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'I' declare @MessageType varchar5(1) = 'I' declare @WarFehlerhaft int = 0 declare @CountElementsChanged int = 0 declare @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY set 6nocount on if exists (select top 1 1 from DialogDatabase d where d.UpdatePhase not in ( 0) ) and @force = 0 begin goto endLabel end select @musterTrigger7 = N'alter %type% "%table%" enable trigger %triggername% ' insert into @elements(LongIdent1, Ident2, LongIdent2) select distinct o.name , case o.type when8 'U' then 'Table'  else '' end as TableType , case @TriggerPattern when '%' then 'all' else '"' + t.name + '"' end as Triggername from sys.tables o join9 sys.triggers t on o.object_id = t.parent_id where t.name != 'QBM_TUIndicator474488' and o.type in( 'U' ) and o.is_memory_optimized = 0 and o.name like10 @TablePattern and t.is_disabled = 1 and ( @UseTriggerPatternForNotLike = 0 and t.name like @TriggerPattern or @UseTriggerPatternForNotLike = 1 and t.name11 not like @TriggerPattern ) order by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount  and @WarFehlerhaft12 = 0 begin select top 1 @TabAktuell = bu.LongIdent1 , @TableType = bu.Ident2 , @TriggerName = bu.LongIdent2 from @elements bu where bu.ElementIndex = @ElementIndex13 select @SQLcmd = replace(replace(replace(@musterTrigger , '%table%', @TabAktuell) , '%type%', @TableType) , '%triggername%', @Triggername) if @DebugSwitch14 > 0 begin print @SQLcmd end  BEGIN TRY  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 1510  , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0  , @ExecuteWithTransact = 0 select @CountElementsChanged += 1 exec QBM_PSessionErrorClean END16 TRY BEGIN CATCH if @HandleErrorSilent = 0 begin delete @ErrorBuffer insert into @ErrorBuffer (ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName17, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select concat('#LDS#Possible invalid foreign keys, run consistency checks: {0}|'18 , ERROR_MESSAGE() , '|' ), 18, 1, 50000 , object_name(@@PROCID), ERROR_LINE(), GETUTCDATE() , null, 0, 0, @SQLcmd exec QBM_PSessionErrorAdd @ErrorBuffer19 exec QBM_PJournal 'SessionError', @@procid, 'E', @DebugLevel, 20 exec QBM_PSessionErrorClean end  select @WarFehlerhaft = 1 END CATCH select @ElementIndex20 += 1 end  if @CountElementsChanged > 0 and exists (select top 1 1 from sys.objects o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage21 = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid), '') + '|' + @TablePattern + '|' + dbo.QBM_FCVIntToString22(@CountElementsChanged) + '|'  if not exists (select top 1 1 from DialogDatabase where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select 23top 1 1 from sys.tables t with (readpast) join sys.columns c with (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 24'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage, @@procid, @MessageType, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage25, @@procid, 'I', 'I' end end exec QBM_PIndicatorChange 1 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow26() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  return (@WarFehlerhaft) end 27