dbo.QBM_FSQTriggerPartCEF

1     create   function dbo.QBM_FSQTriggerPartCEF(@TableForTrigger varchar(30), @OperationType varchar(1) , @IsForCheckExecutable bit  ) returns 
2nvarchar(max) as begin  declare @ColumnToCheck varchar(30) declare @ColumnForTrigger varchar(30)  , @Param01 nvarchar(1024) , @Param02 nvarchar(1024) ,
3 @Param03 nvarchar(1024) , @Param04 nvarchar(1024) , @Param05 nvarchar(1024) declare @SelectParam01 nvarchar(max) declare @SelectParam02 nvarchar(max) 
4declare @SelectParam03 nvarchar(max) declare @SelectParam04 nvarchar(max) declare @SelectParam05 nvarchar(max) declare @ColListTemplate nvarchar(max) declare
5 @WhereClauseCustom nvarchar(max) declare @TableToCheck varchar(30) declare @UID_QBMCEFDefinition varchar(38) declare @PKName1 varchar(30) declare @HasXObjectKey
6 bit declare @WhereClause nvarchar(max) declare @erg nvarchar(max) = N'' declare @SchemaDatatype varchar(128) declare @AliasOld varchar(16) = 'old' declare
7 @AliasNew varchar(16) = 'new' declare @LeererAnfang nvarchar(max) = N'' declare @Operation varchar(16) select @Operation = case @OperationType when 'I'
8 then 'Insert' when 'U' then 'Update' when 'D' then 'Delete' else '<invalid operation>' end declare @elements table (ElementIndex int identity NOT NULL
9 , ColumnToCheck varchar(30) collate database_default  , TableToCheck varchar(30) collate database_default  , ColumnForTrigger varchar(30) collate database_default
10  , SchemaDatatype varchar(128) collate database_default , WhereClause nvarchar(max) collate database_default , Param01 nvarchar(1024) collate database_default
11 NULL , Param02 nvarchar(1024) collate database_default NULL , Param03 nvarchar(1024) collate database_default NULL , Param04 nvarchar(1024) collate database_default
12 NULL , Param05 nvarchar(1024) collate database_default NULL , UID_QBMCEFDefinition varchar(38) , PKName1 varchar(30) collate database_default , HasXObjectKey
13 bit default 0 ) declare @ElementCount int declare @ElementIndex int declare @InsertList nvarchar(max) = N'' declare @ColumnsToReplace QBM_YParameterList
14   declare @Morpheme QBM_YCursorBuffer  if @IsForCheckExecutable = 0 begin select @InsertList = '
15insert into QBMCEFMessage (UID_QBMCEFMessage 
16							, Operation -- I U D	
17							, ParamValue01 , ParamValue02 , ParamValue03 , ParamValue04 , ParamValue05 
18							, ObjectKeyRelated 
19							, UID_QBMCEFDefinition
20							, XObjectKey 
21							, MessageDate 
22							, LogonUser 
23							)
24		'
25 end select @InsertList = concat(@InsertList, '
26', case @IsForCheckExecutable when 1 then ' union all ' else '' end ,'
27select ', case @IsForCheckExecutable
28 when 1 then ' top 1 1 as spalte' else '
29		 x.UID_QBMCEFMessage 
30		, left(upper(x.Operation),1) as Operation -- I U D
31		, x.ParamValue1 , x.ParamValue2 , x.ParamValue3 , x.ParamValue4 , x.ParamValue5 	
32		, x.ObjectKeyRelated
33		, x.UID_QBMCEFDefinition
34		, dbo.QBM_FCVElementToObjectKey1(''QBMCEFMessage'', ''UID_QBMCEFMessage'', x.UID_QBMCEFMessage) as XObjectKey
35		, @Xdate as MessageDate
36		, @ActionUser as LogonUser
37		'
38 end , '
39 from ( ' )   declare @DeletedName varchar(30) = 'deleted' declare @InsertedName varchar(30) = 'inserted' declare @SelectList nvarchar(max) declare
40 @FromList nvarchar(max) declare @JoinList nvarchar(max) insert into @elements(ColumnToCheck, TableToCheck , ColumnForTrigger , SchemaDatatype, WhereClause
41 , Param01 , Param02 , Param03, Param04 , Param05 , UID_QBMCEFDefinition , PKName1, HasXObjectKey ) select x.ColumnToCheck, x.TableToCheck, x.ColumnForTrigger
42, x.SchemaDataType, x.WhereClause , Param01 , Param02 , Param03, Param04 , Param05 , x.UID_QBMCEFDefinition , x.PKName1, x.HasXObjectKey from ( select 
43isnull(t.TableName, tv.TableName) as TableForTrigger, tv.Tablename as TableToCheck, cv.ColumnName as ColumnToCheck, isnull(cb.ColumnName, cv.ColumnName
44) as ColumnForTrigger  , cb.SchemaDataType, cef.WhereClause , cef.Param01 , cef.Param02 , cef.Param03, cef.Param04 , cef.Param05 , cef.UID_QBMCEFDefinition
45 , isnull(t.PKName1, tv.PKName1) as PKName1 , case when xobj.ColumnName is null then 0 else 1 end as HasXObjectKey from QBMCEFDefinition cef join DialogTable
46 tv on cef.UID_DialogTable = tv.UID_DialogTable left outer join DialogTable t on tv.UID_DialogTableBase = t.UID_DialogTable left outer join DialogColumn
47 cv on cef.UID_DialogColumn = cv.UID_DialogColumn left outer join DialogColumn cb on cv.UID_BaseColumn = cb.UID_DialogColumn left outer join DialogColumn
48 xobj on xobj.UID_DialogTable = isnull(t.UID_DialogTable, tv.UID_DialogTable) and xobj.ColumnName = 'XObjectKey' where cef.IsInActive = 0 and dbo.QBM_FGIConfigparmValue
49('QBM\CEF') = '1' and tv.TableType in ('V', 'T') and cef.OperationType = @OperationType and tv.PKName1 > ' ' and (  xobj.ColumnName is not null or  (isnull
50(t.isMNTable, tv.IsMNTable) = 0 and isnull(t.UsageType, tv.UsageType) in ('USERDATA', 'HISTORY') ) ) ) as x where x.TableForTrigger = @TableForTrigger 
51select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select @whereclause = @LeererAnfang select top 1 
52@ColumnToCheck = e.ColumnToCheck , @TableToCheck = e.TableToCheck , @ColumnForTrigger = e.ColumnForTrigger , @SchemaDatatype = e.SchemaDatatype , @WhereClauseCustom
53 = case trim(isnull(e.WhereClause,'')) when '' then '1=1' else e.WhereClause end , @Param01 = e.Param01, @Param02 = e.Param02, @Param03 = e.Param03, @Param04
54 = e.Param04, @Param05 = e.Param05 , @UID_QBMCEFDefinition = e.UID_QBMCEFDefinition , @PKName1 = e.PKName1 , @HasXObjectKey = e.HasXObjectKey from @elements
55 e where e.ElementIndex = @ElementIndex if @IsForCheckExecutable = 1 begin select @DeletedName = @TableForTrigger , @InsertedName = @TableForTrigger end
56 if @OperationType = 'U'  and @IsForCheckExecutable = 0 begin select @erg = concat(@erg , char(13), char(10), N'if update(' , @ColumnForTrigger , ')' ,
57 char(13), char(10) , ' begin' , char(13), char(10) )  end select @AliasOld = case @OperationType when 'I' then 'new' when 'U' then 'old' when 'D' then
58 'old' end select @AliasNew = case @OperationType when 'I' then 'new' when 'U' then 'new' when 'D' then 'old' end select @SelectParam01 = dbo.QBM_FSQCEFParmSelect
59(@TableToCheck , @Param01 , @AliasNew , 'fk01' ) select @SelectParam02 = dbo.QBM_FSQCEFParmSelect(@TableToCheck , @Param02 , @AliasNew , 'fk02' ) select
60 @SelectParam03 = dbo.QBM_FSQCEFParmSelect(@TableToCheck , @Param03 , @AliasNew , 'fk03' ) select @SelectParam04 = dbo.QBM_FSQCEFParmSelect(@TableToCheck
61 , @Param04 , @AliasNew , 'fk04' ) select @SelectParam05 = dbo.QBM_FSQCEFParmSelect(@TableToCheck , @Param05 , @AliasNew , 'fk05' ) select @SelectList 
62= concat(@LeererAnfang, char(13), char(10), char(9)  , char(13), char(10) , case @IsForCheckExecutable when 1 then ' select newid() ' else 'select @UID_QBMCEFMessage'
63 + dbo.QBM_FCVIntToString(@ElementIndex) end , ' as UID_QBMCEFMessage' , char(13), char(10), ', ''' , @Operation, ''' as Operation'   , char(13), char(10
64), ', ', dbo.QBM_FCVStringToIndent(@SelectParam01, 2), ' as ParamValue1 ', '/* ', @Param01 , '*/' , char(13), char(10), ', ', dbo.QBM_FCVStringToIndent
65(@SelectParam02, 2), ' as ParamValue2 ', '/* ', @Param02 , '*/' , char(13), char(10), ', ', dbo.QBM_FCVStringToIndent(@SelectParam03, 2), ' as ParamValue3 '
66, '/* ', @Param03 , '*/' , char(13), char(10), ', ', dbo.QBM_FCVStringToIndent(@SelectParam04, 2), ' as ParamValue4 ', '/* ', @Param04 , '*/' , char(13
67), char(10), ', ', dbo.QBM_FCVStringToIndent(@SelectParam05, 2), ' as ParamValue5 ', '/* ', @Param05 , '*/' ) if @HasXObjectKey = 1 begin select @SelectList
68 = concat(@SelectList, char(13), char(10), ', ', @AliasNew , '.' , 'XObjectKey as ObjectKeyRelated') end else begin select @SelectList = concat(@SelectList
69, char(13), char(10), ', ', '''<Key><T>', @TableToCheck, '</T><P>'' + ', @AliasNew, '.', @PKName1, ' + ''</P></Key>'' as ObjectKeyRelated') end  select
70 @SelectList = concat(@SelectList, char(13), char(10), ', ''', @UID_QBMCEFDefinition, ''' as UID_QBMCEFDefinition ' )  select @JoinList = concat(@LeererAnfang
71, char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param01 , @AliasNew , 'fk01' ),5) , char(13), char(10), dbo.QBM_FCVStringToIndent
72(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param02 , @AliasNew , 'fk02' ),5) , char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck
73 , @Param03 , @AliasNew , 'fk03' ),5) , char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param04 , @AliasNew , 'fk04' 
74),5) , char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param05 , @AliasNew , 'fk05' ),5) )  if @Operation = 'Update' 
75begin select @FromList = concat(@LeererAnfang , char(9), N'from ', @DeletedName ,' ', @AliasOld , ' join ' , @TableForTrigger , N' ', @AliasNew ,' on '
76 , dbo.QBM_FSQTableJoin(@TableForTrigger , @AliasOld, @AliasNew) ) select @WhereClause = concat(@LeererAnfang ,char(13), char(10), char(9), 'where ' + 
77dbo.QBM_FSQIsNullClauseCmp(@TableForTrigger, @ColumnForTrigger, @AliasOld) , ' <> ' , dbo.QBM_FSQIsNullClauseCmp(@TableForTrigger, @ColumnForTrigger, @AliasNew
78) , case when @SchemaDatatype like '%char%' then ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ ' else '' end )   end if @Operation = 'Insert' begin
79 select @FromList = concat(N'' , char(9), N'from ',@InsertedName,' ', @AliasNew,' ' ) select @WhereClause = concat( char(13), char(10), char(9), 'where 1=1'
80 )  end if @Operation = 'delete' begin select @FromList = concat( char(9), N'from ',@DeletedName,' ', @AliasOld,' ' ) select @WhereClause = concat( char