Back to OIM Explorer

dbo.QBM_PDatabaseTriggerEnable

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.489 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_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL103 lines
1CREATE PROCEDURE QBM_PDatabaseTriggerEnable(2  @TriggerPattern nvarchar(64) = '%',3  @UseTriggerPatternForNotLike BIT = 04)5AS6BEGIN7  DECLARE @musterTrigger nvarchar(1024)8  DECLARE @SQLcmd nvarchar(max)9  DECLARE @Triggername nvarchar(64)10  DECLARE @elements QBM_YCursorBuffer11  DECLARE @ElementCount int12  DECLARE @ElementIndex int13  DECLARE @JournalMessage nvarchar(1000)14  DECLARE @DebugSwitch int = 015  DECLARE @DebugLevel varchar(1) = 'I'16  DECLARE @MessageType varchar(1) = 'W'17  BEGIN TRY18    SET nocount19      ON20    SELECT @musterTrigger = N 'enable trigger %triggername% on database '21    INSERT INTO @elements(LongIdent1)22    SELECT CASE @TriggerPattern23    WHEN '%' THEN24    'all'25    ELSE '"' + t.name + '"'26    END AS Triggername27    FROM sys.triggers t28    WHERE29      parent_class = 0 AND is_ms_shipped = 0 AND t.is_disabled = 1 AND(@UseTriggerPatternForNotLike = 0 AND t.name LIKE @TriggerPattern OR @UseTriggerPatternForNotLike30    = 1 AND t.name NOT LIKE @TriggerPattern)31    ORDER BY 132    SELECT @ElementCount = @@ROWCOUNT33    SELECT @ElementIndex = 134    WHILE @ElementIndex <= @ElementCount35    BEGIN36      SELECT TOP 1 @TriggerName = bu.LongIdent137      FROM @elements bu38      WHERE39        bu.ElementIndex = @ElementIndex40      SELECT41        @SQLcmd = replace(@musterTrigger,42        '%triggername%',43        @Triggername)44      IF @DebugSwitch > 045      BEGIN46        print @SQLcmd47      END48      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,49        @LockTimeout_ms = 2500,50        @MaxWaitTimeForLock_s = 10,51        @ProcIDForJournal = @@procid,52        @HandleErrorSilent = 0,53        @ExecuteWithTransact = 054      SELECT @ElementIndex += 155    END56    IF @ElementCount > 0 AND EXISTS(57      SELECT TOP 1 158      FROM sys.objects o59      WHERE60        o.name = 'QBM_PJournal' AND type = 'P')61    BEGIN62      SELECT63        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),64        '') + '|' + @TriggerPattern + '|' + dbo.QBM_FCVIntToString(@ElementCount) + '|'65      IF NOT EXISTS(66        SELECT TOP 1 167        FROM DialogDatabase68        WHERE69          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(70      SELECT TOP 1 171      FROM sys.tables t72        WITH(readpast)73      JOIN sys.columns c74        WITH(readpast)75        ON t.object_id = c.object_id76      WHERE77        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')78      BEGIN79        EXEC QBM_PJournal @JournalMessage,80          @@procid,81          @MessageType,82          @DebugLevel83      END84      ELSE85      BEGIN86        EXEC QBM_PJournal @JournalMessage,87          @@procid,88        'I',89        'I'90      END91    END92  END TRY93  BEGIN CATCH94    EXEC QBM_PSessionErrorAdd DEFAULT95    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()96    RAISERROR(@Rethrow,97    18,98    1)99      WITH NOWAIT100  END CATCH101  endLabel:102  RETURN103END
Open raw exported source
SQL ยท Raw17 lines
1  create   procedure QBM_PDatabaseTriggerEnable (@TriggerPattern nvarchar(64) = '%' , @UseTriggerPatternForNotLike bit = 0 ) as begin declare @musterTrigger2 nvarchar(1024) declare @SQLcmd nvarchar(max) declare @Triggername nvarchar(64) declare @elements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex3 int declare @JournalMessage nvarchar(1000) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'I' declare @MessageType varchar(1) = 'W' BEGIN4 TRY set nocount on select @musterTrigger = N'enable trigger %triggername% on database ' insert into @elements(LongIdent1) select case @TriggerPattern 5when '%' then 'all' else '"' + t.name + '"' end as Triggername from sys.triggers t  where parent_class = 0  and is_ms_shipped = 0  and t.is_disabled = 61 and ( @UseTriggerPatternForNotLike = 0 and t.name like @TriggerPattern or @UseTriggerPatternForNotLike = 1 and t.name not like @TriggerPattern ) order7 by 1 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TriggerName = bu.LongIdent1 from8 @elements bu where bu.ElementIndex = @ElementIndex select @SQLcmd = replace(@musterTrigger, '%triggername%', @Triggername) if @DebugSwitch > 0 begin print9 @SQLcmd end  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10 , @ProcIDForJournal = @@procid10 , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  if @ElementCount > 0 and exists (select top 1 1 from sys.objects o 11where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|'12 + isnull(OBJECT_NAME(@@procid), '') + '|' + @TriggerPattern + '|' + dbo.QBM_FCVIntToString(@ElementCount) + '|'  if not exists (select top 1 1 from DialogDatabase13 where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select top 1 1 from sys.tables t with (readpast) join sys.columns c with (readpast) on 14t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage, @@procid, @MessageType15, @DebugLevel end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare16 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  return end 17