Back to OIM Explorer

dbo.QBM_PTriggerDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.530 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_FGIObjectIsDropable source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FSQObjectComment source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_TUIndicator474488 source text reference

References

Referenced By

Complete Source

SQL190 lines
1CREATE PROCEDURE QBM_PTriggerDrop(2  @pattern nvarchar(255),3  @TablePattern nvarchar(256) = '%',4  @force BIT = 0,5  @CustomTriggerAlso BIT = 06)7AS8BEGIN9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @triggername varchar(64)11  DECLARE @ownerName nvarchar(64)12  DECLARE @TableName varchar(64)13  DECLARE @elementsToDrop TABLE(ElementIndex int identity,14  Int1 int DEFAULT 0,15  ContentShort nvarchar(400) collate database_default,16  LongIdent1 nvarchar(256) collate database_default,17  LongIdent2 nvarchar(256) collate database_default,18  ContentFull nvarchar(max) collate database_default,19  Ident1 varchar(64) collate database_default,20  Ident3 varchar(64) collate database_default)21  DECLARE @ElementCount int22  DECLARE @ElementIndex int23  DECLARE @DebugSwitch int = 024  DECLARE @JournalMessage nvarchar(1000)25  DECLARE @DebugLevel varchar(1) = 'I'26  DECLARE @MessageType varchar(1) = 'W'27  DECLARE @LengthMessage nvarchar(256) = '#LDS#Name of Element {0} is too long (30).|'28  DECLARE @CountElementsChanged int = 029  DECLARE @CommentCode BIT = 030  SET XACT_ABORT OFF31  BEGIN TRY32    IF @pattern NOT LIKE '%[%]%' AND @TablePattern = '%' AND @force = 0 AND @CustomTriggerAlso = 033    BEGIN34      IF EXISTS(35        SELECT TOP 1 136        FROM sys.triggers tr37        WHERE38          tr.name = @pattern AND tr.name != 'QBM_TUIndicator474488')39      BEGIN40        SELECT41          @SQLcmd = CONCAT('drop trigger if exists ',42          @pattern)43        EXEC sp_executesql @SQLCmd44        IF @pattern NOT LIKE 'GEN[_]%'45        BEGIN46          SELECT @CountElementsChanged = 147        END48      END49      GOTO verarbeitet50    END51    IF @@NESTLEVEL < 252    BEGIN53      IF EXISTS(54        SELECT TOP 1 155        FROM sys.tables t56        WHERE57          t.name = 'QBMModuleDef')58      BEGIN59        IF EXISTS(60          SELECT TOP 1 161          FROM QBMModuleDef d62          WHERE63            d.UID_ModuleDef = 'MDK-Moduledefinition') AND EXISTS(64        SELECT TOP 1 165        FROM information_schema.routines r66        WHERE67          r.ROUTINE_type = N 'function' AND r.ROUTINE_NAME = 'QBM_FSQObjectComment')68        BEGIN69          SELECT @CommentCode = 170        END71      END72    END73    SELECT74      @SQLcmd = '75		select t.name, tr.name, o.name76		from sys.triggers tr with (readpast) join sys.objects t with (readpast) on tr.parent_id = t.object_id77							join sys.schemas o with (readpast) on t.schema_id = o.schema_id 78												and dbo.QBM_FGIObjectIsDropable (o.schema_id)=179												and o.name = ''dbo''80		where tr.name like '''81      + @pattern + '''82			and t.name like ''' + @TablePattern + '''83			and tr.name != ''QBM_TUIndicator474488''84		'85    IF @CustomTriggerAlso = 086    BEGIN87      SELECT @SQLcmd = @SQLcmd + '88			and tr.name not like ''CCC[_]%''89			'90    END91    SELECT92      @SQLcmd = CONCAT(@SQLcmd,93      '94						order by t.object_id, tr.name')95    IF @DebugSwitch > 096    BEGIN97      print @sqlcmd98    END99    INSERT INTO @elementsToDrop(Ident1,100    LongIdent1,101    Ident3)102    EXEC sp_executesql @sqlcmd103    SELECT @ElementCount = @@ROWCOUNT104    SELECT @ElementIndex = 1105    WHILE @ElementIndex <= @ElementCount106    BEGIN107      SELECT108        TOP 1 @TableName = bu.Ident1,109        @triggername = bu.LongIdent1,110        @ownerName = bu.Ident3111      FROM @elementsToDrop bu112      WHERE113        bu.ElementIndex = @ElementIndex114      IF len(@triggername) > 30 AND @force = 0115      BEGIN116        SELECT @LengthMessage = @LengthMessage + @triggername + '|'117        RAISERROR(@LengthMessage,118        18,119        1)120          WITH nowait121      END122      IF len(@TableName) > 30 AND @force = 0123      BEGIN124        SELECT @LengthMessage = @LengthMessage + @TableName + '|'125        RAISERROR(@LengthMessage,126        18,127        1)128          WITH nowait129      END130      SELECT @SQLcmd = N 'drop trigger if exists ' + @ownerName + N '.' + @triggername131      IF @DebugSwitch > 0132      BEGIN133        print @sqlcmd134      END135      IF @CommentCode = 1136      BEGIN137        print dbo.QBM_FSQObjectComment(@triggername)138      END139      EXEC sp_executeSQL @SQLcmd140      SELECT @CountElementsChanged += 1141      SELECT @ElementIndex += 1142    END143    verarbeitet:144    IF @CountElementsChanged > 0 AND EXISTS(145      SELECT TOP 1 1146      FROM sys.objects o147      WHERE148        o.name = 'QBM_PJournal' AND type = 'P')149    BEGIN150      SELECT151        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),152        '') + '|' + @pattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'153      IF NOT EXISTS(154        SELECT TOP 1 1155        FROM DialogDatabase156        WHERE157          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(158      SELECT TOP 1 1159      FROM sys.tables t160        WITH(readpast)161      JOIN sys.columns c162        WITH(readpast)163        ON t.object_id = c.object_id164      WHERE165        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')166      BEGIN167        EXEC QBM_PJournal @JournalMessage,168          @@procid,169          @MessageType,170          @DebugLevel,171        20172      END173      ELSE174      BEGIN175        EXEC QBM_PJournal @JournalMessage,176          @@procid,177        'I',178        'I'179      END180    END181  END TRY182  BEGIN CATCH183    EXEC QBM_PSessionErrorAdd DEFAULT184    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()185    RAISERROR(@Rethrow,186    18,187    1)188      WITH NOWAIT189  END CATCH190END
Open raw exported source
SQL ยท Raw41 lines
1   create   procedure QBM_PTriggerDrop (@pattern nvarchar(255) , @TablePattern nvarchar(256) = '%' , @force bit = 0  , @CustomTriggerAlso bit = 20 )  as begin  declare @SQLcmd nvarchar(max) DECLARE @triggername varchar(64) declare @ownerName nvarchar(64) declare @TableName varchar(64)     declare3 @elementsToDrop  table (ElementIndex int identity , Int1 int default 0 , ContentShort nvarchar(400) collate database_default , LongIdent1 nvarchar(2564) collate database_default , LongIdent2 nvarchar(256) collate database_default , ContentFull nvarchar(max) collate database_default , Ident1 varchar(645) collate database_default , Ident3 varchar(64) collate database_default ) declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int6 = 0 declare @JournalMessage nvarchar(1000) declare @DebugLevel varchar(1) = 'I' declare @MessageType varchar(1) = 'W' declare @LengthMessage nvarchar(2567) = '#LDS#Name of Element {0} is too long (30).|' declare @CountElementsChanged int = 0 declare @CommentCode bit = 0 SET XACT_ABORT OFF BEGIN TRY if @pattern8 not like '%[%]%' and @TablePattern = '%' and @force = 0 and @CustomTriggerAlso = 0 begin if exists (select top 1 1 from sys.triggers tr where tr.name 9= @pattern and tr.name != 'QBM_TUIndicator474488' ) begin select @SQLcmd = CONCAT('drop trigger if exists ', @pattern) exec sp_executesql @SQLCmd if @pattern10 not like 'GEN[_]%' begin select @CountElementsChanged = 1 end end  goto verarbeitet end if @@NESTLEVEL < 2  begin if exists (select top 1 1 from sys.tables11 t where t.name = 'QBMModuleDef' ) begin if exists (select top 1 1 from QBMModuleDef d where d.UID_ModuleDef = 'MDK-Moduledefinition' ) and exists (select12 top 1 1 from information_schema.routines r where r.ROUTINE_type = N'function' and r.ROUTINE_NAME = 'QBM_FSQObjectComment' ) begin select @CommentCode 13= 1   end end end  select @SQLcmd = '14		select t.name, tr.name, o.name15		from sys.triggers tr with (readpast) join sys.objects t with (readpast) on tr.parent_id = t.object_id16							join sys.schemas o with (readpast) on t.schema_id = o.schema_id 17												and dbo.QBM_FGIObjectIsDropable (o.schema_id)=118												and o.name = ''dbo''19		where tr.name like '''20 + @pattern + '''21			and t.name like ''' + @TablePattern + '''22			and tr.name != ''QBM_TUIndicator474488''23		' if @CustomTriggerAlso = 0 begin select24 @SQLcmd = @SQLcmd + '25			and tr.name not like ''CCC[_]%''26			' end select @SQLcmd = concat( @SQLcmd, '27						order by t.object_id, tr.name' ) if @DebugSwitch28 > 0 begin print @sqlcmd end insert into @elementsToDrop(Ident1, LongIdent1, Ident3) exec sp_executesql @sqlcmd select @ElementCount = @@ROWCOUNT select29 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1 , @triggername = bu.LongIdent1 , @ownerName = bu.Ident330 from @elementsToDrop bu where bu.ElementIndex = @ElementIndex if len(@triggername) > 30 and @force = 0 begin select @LengthMessage = @LengthMessage + 31@triggername + '|' raiserror(@LengthMessage, 18, 1) with nowait end if len(@TableName) > 30 and @force = 0 begin select @LengthMessage = @LengthMessage32 + @TableName + '|' raiserror(@LengthMessage, 18, 1) with nowait end     select @SQLcmd = N'drop trigger if exists ' + @ownerName + N'.' + @triggername33 if @DebugSwitch > 0 begin print @sqlcmd end if @CommentCode = 1 begin print dbo.QBM_FSQObjectComment (@triggername) end exec sp_executeSQL @SQLcmd select34 @CountElementsChanged += 1  select @ElementIndex += 1 end   verarbeitet: if @CountElementsChanged > 0 and exists (select top 1 1 from sys.objects o where35 o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' +36 isnull(OBJECT_NAME(@@procid), '') + '|' + @pattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'  if not exists (select top 1 1 from DialogDatabase37 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 38t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage, @@procid, @MessageType39, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare40 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 41