dbo.QBM_PTriggerWatchCreate

1                     create   procedure QBM_PTriggerWatchCreate (@TableName varchar(30),  @Operation varchar(16)  , @TriggerName varchar(30) ) as
2 begin  declare @TriggerBody nvarchar(max) = '' declare @SQLcmd nvarchar(max) declare @SaveToQBMBufferConfig nvarchar(max) = '' declare @VarDefinition 
3nvarchar(max) = '	declare @XUser nvarchar(64) = object_name(@@procid)
4	declare @Xdate datetime = getutcdate()' declare @tabletype nvarchar(1)  declare
5 @IsWatch bit = 0 declare @IsSemaphor bit = 0 declare @IsResidentTable bit = 0 declare @IsInsteadOf bit = 0  declare @IsXOriginHandling bit = 0  declare
6 @IsXIsInEffectHandling bit = 0  declare @DebugLevel char(1) = 'W' declare @DebugMessage nvarchar(4000) declare @XDateSubItemUpdate nvarchar(max) = '' 
7declare @XMarkedForDeletionChg nvarchar(max) = '' declare @SplittedLookup nvarchar(max) = '' declare @CEF_SIEM nvarchar(max) declare @DebugSwitch int =
8 0 declare @IsTableRevision bit = 0 declare @TableRevision nvarchar(max) declare @IsDeactivatedByPreProcessor bit SET XACT_ABORT OFF BEGIN TRY select top
9 1 @tabletype = t.TableType , @IsResidentTable = t.IsResident , @IsDeactivatedByPreProcessor = t.IsDeactivatedByPreProcessor from DialogTable t with (readpast
10) where TableName = @TableName and t.TableType not in ('M')  if @tabletype in ('R', 'P', 'U') begin select @IsInsteadOf = 1 end if @Operation = 'UPDATE'
11 begin select @SaveToQBMBufferConfig = dbo.QBM_FSQBufferConfigSaveOrigin(@TableName ) end select @TableRevision = dbo.QBM_FSQWatchRevision(@TableName ,
12 @Operation) if @TableRevision > ' ' begin select @IsTableRevision = 1 end  if @TableName in (select TableName from QBM_VHeavyLoadTables where TableName
13 <> 'DialogJournal'  ) begin goto ConfigBufferOnly end if @TableName = 'DialogJournal' begin goto CEFSIEMOnly end if @IsDeactivatedByPreProcessor = 1 begin
14 goto dropOnly end if exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = @TableName and c.COLUMN_NAME = 'XOrigin' ) and exists (select
15 top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = @TableName and c.COLUMN_NAME = 'XObjectKey' )  begin select @IsXOriginHandling = 1 end if @IsXOriginHandling
16 = 1 and exists (select top 1 1 from QBM_VSchemaColumns c where c.TABLE_NAME = @TableName and c.COLUMN_NAME = 'XIsInEffect' ) begin select @IsXIsInEffectHandling
17 = 1 end  if @tabletype = 'V' begin goto DropOnly end if not exists (select top 1 1 from information_schema.tables where table_name = @TableName and table_type
18 in ('BASE TABLE', 'VIEW') ) begin  goto DropOnly end if '1' = dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog') begin if @IsXOriginHandling
19 = 1 begin if @Operation = 'Update' begin if exists (select top 1 1 from dbo.QBM_FTColumnsToWatch(@TableName , 'Delete') ) or exists (select top 1 1 from
20 dbo.QBM_FTColumnsToWatch(@TableName , 'Update' ) ) begin select @IsWatch = 1 end end  if @Operation = 'Insert' begin if exists (select top 1 1 from dbo.QBM_FTColumnsToWatch
21(@TableName , 'Insert') ) begin select @IsWatch = 1 end end  end else  begin if exists (select top 1 1 from dbo.QBM_FTColumnsToWatch(@TableName , @Operation
22) ) begin select @IsWatch = 1 end end  end             if exists (select  top 1 1 from DialogTable t with (readpast) join DialogColumn c with (readpast
23) on c.UID_DialogTable = t.UID_DialogTable join DialogColumnHasSemaphor chs with (readpast) on c.UID_DialogColumn = chs.UID_DialogColumn where t.TableName
24 = @TableName and ( c.IsPKMember = 1 and @Operation = 'insert' or c.IsPKMember = 0 and @Operation = 'update' or c.IsPKMember = 1 and @Operation = 'delete'
25 ) ) begin select @IsSemaphor = 1 end select @XDateSubItemUpdate = dbo.QBM_FSQXDateSubItemUpdate(@TableName, @Operation) select @XMarkedForDeletionChg 
26= dbo.QBM_FSQXMarkedForDeletionChg(@TableName, @Operation) select @SplittedLookup = dbo.QBM_FSQTriggerSplittedLookup(@TableName, @Operation) if @IsWatch
27 = 0 and @IsInsteadOf = 0 and @IsSemaphor = 0 and @IsResidentTable = 0 and @SaveToQBMBufferConfig = '' and @IsXOriginHandling = 0 and @XDateSubItemUpdate
28 = '' and @XMarkedForDeletionChg = '' and @SplittedLookup = '' and @IsTableRevision = 0 begin  if @DebugSwitch > 0 begin print 'drop only' end goto DropOnly
29 end if @IsWatch = 1 and @Operation in ('UPDATE', 'DELETE') begin select @VarDefinition = concat(@Vardefinition , N'
30 declare @DialogWatchProperty QBM_YWatchBufferProp
31 declare @DialogWatchOperation QBM_YWatchBufferOp
32
33'
34) end if @XDateSubItemUpdate > ' ' begin select @VarDefinition = concat(@VarDefinition , '
35	declare @UID_TaskXDateSubItemUpdate varchar(38) 
36	declare @JustNowVarchar varchar(64) = dbo.QBM_FCVDatetimeToStringUTC(GetUTCDate())
37	'
38 ) end if @IsXOriginHandling = 1 and @Operation in ('UPDATE') begin select @VarDefinition = concat(@VarDefinition , N'
39	declare @EntriesToFire QBM_YParameterList 
40	declare @AdditionalEntries QBM_YParameterList
41	declare @StartAt datetime'
42 ) end if @IsSemaphor = 1 or @XDateSubItemUpdate > ' ' or @XMarkedForDeletionChg > ' ' begin select @VarDefinition = concat(@VarDefinition , N'
43	declare @DBQueueElements QBM_YDBQueueRaw '
44 ) end if @IsSemaphor = 1 begin select @VarDefinition = concat(@VarDefinition , N'
45	declare @IsSemaphorInFullsync bit  = sign(len(dbo.QBM_FGISessionContext (''Fullsync'')))'
46 ) end  if @tabletype in ('R', 'P', 'U') begin  select @TriggerBody = concat(N'	raiserror (N''#LDS#Operations on table {0} are not permitted.|' , rtrim
47(@TableName ) , N'|'', 18, 1) with nowait') end else begin if @IsSemaphor = 1 begin select @Triggerbody = concat(@Triggerbody , dbo.QBM_FSQTriggerWatchSemaphor
48 (@TableName , @operation)) if @DebugSwitch > 0 begin select @Debugmessage = concat('semaphor für ' , @TableName , nchar(9) , @operation) exec QBM_PJournal
49 @Debugmessage, @@PROCID, 'D', @DebugLevel end  end if @IsResidentTable = 1 begin  select @Triggerbody = concat(@Triggerbody , N'
50		 exec QBM_ZDialogSemaphorIncGlobal 0, ''USERDATARESIDENT'', '''', @GenProcID
51	 '
52 ) end if @IsWatch = 1 begin select @Triggerbody = concat(@Triggerbody , dbo.QBM_FSQTriggerWatchTable (@TableName , @operation)) end end if @TableRevision
53 > ' ' begin select @TriggerBody = concat(@TriggerBody , @TableRevision) end if @XDateSubItemUpdate > ' ' begin select @TriggerBody = concat(@TriggerBody
54 , @XDateSubItemUpdate) end if @XMarkedForDeletionChg > ' ' begin select @TriggerBody = concat(@TriggerBody , @XMarkedForDeletionChg) end if @IsXOriginHandling
55 = 1 and @Operation in ('UPDATE') begin if @DebugSwitch > 0 begin print '--------XOrigin-Anteil' print dbo.QBM_FSQTriggerWatchXOrigin(@TableName) end select
56 @TriggerBody = concat(@TriggerBody , dbo.QBM_FSQTriggerWatchXOrigin(@TableName)) end if @IsXIsInEffectHandling = 1 and @Operation in ('UPDATE') begin 
57if @DebugSwitch > 0 begin print '--------XIsInEffect-Anteil' print dbo.QBM_FSQTriggerWatchXIsInEffect(@TableName) end select @TriggerBody = concat(@TriggerBody
58 , dbo.QBM_FSQTriggerWatchXIsInEffect(@TableName)) end if @IsXOriginHandling = 1 and @Operation in ('Delete') begin select @TriggerBody = concat(@TriggerBody
59 , N'
60-- Handle Delete via XOrigin
61 if exists (select top 1 1
62			 from deleted
63			 where XOrigin > 0
64			)
65	begin
66		raiserror(''deleting Objects with XOrigin greater then 0 is not permitted'', 18, 1) with nowait
67	end
68		
69-- / Handle Delete via XOrigin
70
71	'
72 ) if @DebugSwitch > 0 begin print '## Delete error included' end end if @SplittedLookup > ' ' begin select @TriggerBody = concat(@TriggerBody , @SplittedLookup
73) end  CEFSIEMOnly: select @CEF_SIEM = dbo.QBM_FSQTriggerPartCEF(@TableName, left(@Operation, 1), 0)  if @CEF_SIEM > ' ' begin select @Triggerbody = concat
74(@Triggerbody , @CEF_SIEM) end  ConfigBufferOnly: select @TriggerBody = concat(@TriggerBody , @SaveToQBMBufferConfig) if @triggerbody > ' ' begin select
75 @SQLcmd = dbo.QBM_FSQTriggerDef (@TriggerName , @TableName , @Operation , concat(N'/* SET XACT_ABORT ON is implicit */ ', nchar(13), nchar(10), @TriggerBody
76 ) , @VarDefinition , concat('Trigger watch for ' , @TableName , '(' , @Operation , ')') ) if @DebugSwitch > 0 begin print substring(@SQLcmd, 1, 4000) 
77print substring(@SQLcmd, 4001, 4000) print substring(@SQLcmd, 8001, 4000) print substring(@SQLcmd, 12001, 4000) print substring(@SQLcmd, 16001, 4000) print
78 substring(@SQLcmd, 20001, 4000) print substring(@SQLcmd, 24001, 4000) print substring(@SQLcmd, 28001, 4000) print substring(@SQLcmd, 32001, 4000) print
79 substring(@SQLcmd, 36001, 4000) print substring(@SQLcmd, 40001, 4000) print substring(@SQLcmd, 44001, 4000) print substring(@SQLcmd, 48001, 4000) print
80 substring(@SQLcmd, 52001, 4000) print substring(@SQLcmd, 56001, 4000) print substring(@SQLcmd, 60001, 4000) print substring(@SQLcmd, 64001, 4000) print