Back to OIM Explorer

dbo.QBM_PDatabaseTriggerDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.353 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

SQL98 lines
1CREATE PROCEDURE QBM_PDatabaseTriggerDrop(2  @TriggerPattern nvarchar(64) = '%'3)4AS5BEGIN6  DECLARE @musterTrigger nvarchar(1024)7  DECLARE @SQLcmd nvarchar(max)8  DECLARE @Triggername nvarchar(64)9  DECLARE @elements TABLE(ElementIndex int identity NOT NULL,10  LongIdent1 nvarchar(256) collate database_default)11  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  SET XACT_ABORT OFF18  BEGIN TRY19    SELECT @musterTrigger = N 'drop trigger if exists "%triggername%" on database '20    INSERT INTO @elements(LongIdent1)21    SELECT t.name AS Triggername22    FROM sys.triggers t23    WHERE24      parent_class = 0 AND is_ms_shipped = 0 AND t.name LIKE @TriggerPattern25    ORDER BY 126    SELECT @ElementCount = count(*)27    FROM @elements28    SELECT @ElementIndex = 129    WHILE @ElementIndex <= @ElementCount30    BEGIN31      SELECT TOP 1 @TriggerName = bu.LongIdent132      FROM @elements bu33      WHERE34        bu.ElementIndex = @ElementIndex35      SELECT36        @SQLcmd = replace(@musterTrigger,37        '%triggername%',38        @Triggername)39      IF @DebugSwitch > 040      BEGIN41        print @SQLcmd42      END43      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,44        @LockTimeout_ms = 2500,45        @MaxWaitTimeForLock_s = 10,46        @ProcIDForJournal = @@procid,47        @HandleErrorSilent = 0,48        @ExecuteWithTransact = 049      SELECT @ElementIndex += 150    END51    IF @ElementCount > 0 AND EXISTS(52      SELECT TOP 1 153      FROM sys.objects o54      WHERE55        o.name = 'QBM_PJournal' AND type = 'P')56    BEGIN57      SELECT58        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),59        '') + '|' + @TriggerPattern + '|' + dbo.QBM_FCVIntToString(@ElementCount) + '|'60      IF NOT EXISTS(61        SELECT TOP 1 162        FROM DialogDatabase63        WHERE64          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(65      SELECT TOP 1 166      FROM sys.tables t67        WITH(readpast)68      JOIN sys.columns c69        WITH(readpast)70        ON t.object_id = c.object_id71      WHERE72        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')73      BEGIN74        EXEC QBM_PJournal @JournalMessage,75          @@procid,76          @MessageType,77          @DebugLevel78      END79      ELSE80      BEGIN81        EXEC QBM_PJournal @JournalMessage,82          @@procid,83        'I',84        'I'85      END86    END87  END TRY88  BEGIN CATCH89    EXEC QBM_PSessionErrorAdd DEFAULT90    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()91    RAISERROR(@Rethrow,92    18,93    1)94      WITH NOWAIT95  END CATCH96  endLabel:97  RETURN98END
Open raw exported source
SQL ยท Raw16 lines
1create   procedure QBM_PDatabaseTriggerDrop (@TriggerPattern nvarchar(64) = '%' ) as begin declare @musterTrigger nvarchar(1024) declare @SQLcmd2 nvarchar(max) declare @Triggername nvarchar(64) declare @elements table (ElementIndex int identity NOT NULL , LongIdent1 nvarchar(256) collate database_default3 ) declare @ElementCount int declare @ElementIndex int declare @JournalMessage nvarchar(1000) declare @DebugSwitch int = 0 declare @DebugLevel varchar(14) = 'I' declare @MessageType varchar(1) = 'W' SET XACT_ABORT OFF BEGIN TRY select @musterTrigger = N'drop trigger if exists "%triggername%" on database '5 insert into @elements(LongIdent1) select t.name as Triggername from sys.triggers t  where parent_class = 0  and is_ms_shipped = 0  and t.name like @TriggerPattern6 order by 1 select @ElementCount = count(*) from @elements select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TriggerName7 = bu.LongIdent1 from @elements bu where bu.ElementIndex = @ElementIndex select @SQLcmd = replace(@musterTrigger, '%triggername%', @Triggername) if @DebugSwitch8 > 0 begin print @SQLcmd end  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10  , @ProcIDForJournal9 = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  if @ElementCount > 0 and exists (select top 1 1 from sys.objects10 o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|'11 + isnull(OBJECT_NAME(@@procid), '') + '|' + @TriggerPattern + '|' + dbo.QBM_FCVIntToString(@ElementCount) + '|'  if not exists (select top 1 1 from DialogDatabase12 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 13t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage, @@procid, @MessageType14, @DebugLevel end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare15 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel:  return end 16