dbo.QBM_PTriggerDrop

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)     declare
3 @elementsToDrop  table (ElementIndex int identity , Int1 int default 0 , ContentShort nvarchar(400) collate database_default , LongIdent1 nvarchar(256
4) collate database_default , LongIdent2 nvarchar(256) collate database_default , ContentFull nvarchar(max) collate database_default , Ident1 varchar(64
5) collate database_default , Ident3 varchar(64) collate database_default ) declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int
6 = 0 declare @JournalMessage nvarchar(1000) declare @DebugLevel varchar(1) = 'I' declare @MessageType varchar(1) = 'W' declare @LengthMessage nvarchar(256
7) = '#LDS#Name of Element {0} is too long (30).|' declare @CountElementsChanged int = 0 declare @CommentCode bit = 0 SET XACT_ABORT OFF BEGIN TRY if @pattern
8 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 @pattern
10 not like 'GEN[_]%' begin select @CountElementsChanged = 1 end end  goto verarbeitet end if @@NESTLEVEL < 2  begin if exists (select top 1 1 from sys.tables
11 t where t.name = 'QBMModuleDef' ) begin if exists (select top 1 1 from QBMModuleDef d where d.UID_ModuleDef = 'MDK-Moduledefinition' ) and exists (select
12 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.name
15		from sys.triggers tr with (readpast) join sys.objects t with (readpast) on tr.parent_id = t.object_id
16							join sys.schemas o with (readpast) on t.schema_id = o.schema_id 
17												and dbo.QBM_FGIObjectIsDropable (o.schema_id)=1
18												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 select
24 @SQLcmd = @SQLcmd + '
25			and tr.name not like ''CCC[_]%''
26			' end select @SQLcmd = concat( @SQLcmd, '
27						order by t.object_id, tr.name' ) if @DebugSwitch
28 > 0 begin print @sqlcmd end insert into @elementsToDrop(Ident1, LongIdent1, Ident3) exec sp_executesql @sqlcmd select @ElementCount = @@ROWCOUNT select
29 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1 , @triggername = bu.LongIdent1 , @ownerName = bu.Ident3
30 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 = @LengthMessage
32 + @TableName + '|' raiserror(@LengthMessage, 18, 1) with nowait end     select @SQLcmd = N'drop trigger if exists ' + @ownerName + N'.' + @triggername
33 if @DebugSwitch > 0 begin print @sqlcmd end if @CommentCode = 1 begin print dbo.QBM_FSQObjectComment (@triggername) end exec sp_executeSQL @SQLcmd select
34 @CountElementsChanged += 1  select @ElementIndex += 1 end   verarbeitet: if @CountElementsChanged > 0 and exists (select top 1 1 from sys.objects o where
35 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 DialogDatabase
37 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, @MessageType
39, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare
40 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 
41