Back to OIM Explorer

dbo.QBM_FSQTriggerPartCEF

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function. References QBM_PJobCreate*

Source: sandbox-db sys.sql_modules

Source size: 14.821 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • References QBM_PJobCreate*

Typed Edges

  • references source dbo.QBM_FCVStringToList source text reference
  • references source dbo.QBM_FCVStringToListSQLMorphem source text reference
  • references source dbo.QBM_FCVStringToListSQLMorphem0 source text reference
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVIntToString source text reference
  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FSQCEFJoin source text reference
  • references source dbo.QBM_FSQCEFParmSelect source text reference
  • references source dbo.QBM_FSQIsNullClauseCmp source text reference
  • references source dbo.QBM_FSQTableJoin source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOTemplate source text reference
  • references source dbo.QBM_PJobCreate_HOTemplate_B source text reference

References

Referenced By

Complete Source

SQL674 lines
1CREATE FUNCTION dbo.QBM_FSQTriggerPartCEF(2  @TableForTrigger varchar(30),3  @OperationType varchar(1),4  @IsForCheckExecutable BIT5) RETURNS nvarchar(max6)7AS8BEGIN9  DECLARE @ColumnToCheck varchar(30)10  DECLARE @ColumnForTrigger varchar(30),11  @Param01 nvarchar(1024),12  @Param02 nvarchar(1024),13  @Param03 nvarchar(1024),14  @Param04 nvarchar(1024),15  @Param05 nvarchar(1024)16  DECLARE @SelectParam01 nvarchar(max)17  DECLARE @SelectParam02 nvarchar(max)18  DECLARE @SelectParam03 nvarchar(max)19  DECLARE @SelectParam04 nvarchar(max)20  DECLARE @SelectParam05 nvarchar(max)21  DECLARE @ColListTemplate nvarchar(max)22  DECLARE @WhereClauseCustom nvarchar(max)23  DECLARE @TableToCheck varchar(30)24  DECLARE @UID_QBMCEFDefinition varchar(38)25  DECLARE @PKName1 varchar(30)26  DECLARE @HasXObjectKey BIT27  DECLARE @WhereClause nvarchar(max)28  DECLARE @erg nvarchar(max) = N ''29  DECLARE @SchemaDatatype varchar(128)30  DECLARE @AliasOld varchar(16) = 'old'31  DECLARE @AliasNew varchar(16) = 'new'32  DECLARE @LeererAnfang nvarchar(max) = N ''33  DECLARE @Operation varchar(16)34  SELECT @Operation = CASE @OperationType35  WHEN 'I' THEN36  'Insert'37  WHEN 'U' THEN38  'Update'39  WHEN 'D' THEN40  'Delete'41  ELSE '<invalid operation>'42  END43  DECLARE @elements TABLE(ElementIndex int identity NOT NULL,44  ColumnToCheck varchar(30) collate database_default,45  TableToCheck varchar(30) collate database_default,46  ColumnForTrigger varchar(30) collate database_default,47  SchemaDatatype varchar(128) collate database_default,48  WhereClause nvarchar(max) collate database_default,49  Param01 nvarchar(1024) collate database_default NULL,50  Param02 nvarchar(1024) collate database_default NULL,51  Param03 nvarchar(1024) collate database_default NULL,52  Param04 nvarchar(1024) collate database_default NULL,53  Param05 nvarchar(1024) collate database_default NULL,54  UID_QBMCEFDefinition varchar(38),55  PKName1 varchar(30) collate database_default,56  HasXObjectKey BIT DEFAULT 0)57  DECLARE @ElementCount int58  DECLARE @ElementIndex int59  DECLARE @InsertList nvarchar(max) = N ''60  DECLARE @ColumnsToReplace QBM_YParameterList61  DECLARE @Morpheme QBM_YCursorBuffer62  IF @IsForCheckExecutable = 063  BEGIN64    SELECT65      @InsertList = '66insert into QBMCEFMessage (UID_QBMCEFMessage 67							, Operation -- I U D	68							, ParamValue01 , ParamValue02 , ParamValue03 , ParamValue04 , ParamValue05 69							, ObjectKeyRelated 70							, UID_QBMCEFDefinition71							, XObjectKey 72							, MessageDate 73							, LogonUser 74							)75		'76  END77  SELECT78    @InsertList = CONCAT(@InsertList,79    '80',81  CASE @IsForCheckExecutable82    WHEN 1 THEN83  ' union all '84  ELSE ''85  END,86  '87select ',88  CASE @IsForCheckExecutable89    WHEN 1 THEN90  ' top 1 1 as spalte'91  ELSE '92		 x.UID_QBMCEFMessage 93		, left(upper(x.Operation),1) as Operation -- I U D94		, x.ParamValue1 , x.ParamValue2 , x.ParamValue3 , x.ParamValue4 , x.ParamValue5 	95		, x.ObjectKeyRelated96		, x.UID_QBMCEFDefinition97		, dbo.QBM_FCVElementToObjectKey1(''QBMCEFMessage'', ''UID_QBMCEFMessage'', x.UID_QBMCEFMessage) as XObjectKey98		, @Xdate as MessageDate99		, @ActionUser as LogonUser100		'101  END,102  '103 from ( ')104  DECLARE @DeletedName varchar(30) = 'deleted'105  DECLARE @InsertedName varchar(30) = 'inserted'106  DECLARE @SelectList nvarchar(max)107  DECLARE @FromList nvarchar(max)108  DECLARE @JoinList nvarchar(max)109  INSERT INTO @elements(ColumnToCheck,110  TableToCheck,111  ColumnForTrigger,112  SchemaDatatype,113  WhereClause,114  Param01,115  Param02,116  Param03,117  Param04,118  Param05,119  UID_QBMCEFDefinition,120  PKName1,121  HasXObjectKey)122  SELECT123    x.ColumnToCheck,124    x.TableToCheck,125    x.ColumnForTrigger,126    x.SchemaDataType,127    x.WhereClause,128    Param01,129    Param02,130    Param03,131    Param04,132    Param05,133    x.UID_QBMCEFDefinition,134    x.PKName1,135    x.HasXObjectKey136  FROM(137  SELECT138    isnull(t.TableName, tv.TableName) AS TableForTrigger,139    tv.Tablename AS TableToCheck,140    cv.ColumnName AS ColumnToCheck,141    isnull(cb.ColumnName, cv.ColumnName) AS ColumnForTrigger,142    cb.SchemaDataType,143    cef.WhereClause,144    cef.Param01,145    cef.Param02,146    cef.Param03,147    cef.Param04,148    cef.Param05,149    cef.UID_QBMCEFDefinition,150    isnull(t.PKName1, tv.PKName1) AS PKName1,151  CASE152    WHEN xobj.ColumnName IS NULL THEN153  0154  ELSE 1155  END AS HasXObjectKey156  FROM QBMCEFDefinition cef157  JOIN DialogTable tv158    ON cef.UID_DialogTable = tv.UID_DialogTable159  LEFT160  OUTER161  JOIN DialogTable t162    ON tv.UID_DialogTableBase = t.UID_DialogTable163  LEFT164  OUTER165  JOIN DialogColumn cv166    ON cef.UID_DialogColumn = cv.UID_DialogColumn167  LEFT168  OUTER169  JOIN DialogColumn cb170    ON cv.UID_BaseColumn = cb.UID_DialogColumn171  LEFT172  OUTER173  JOIN DialogColumn xobj174    ON xobj.UID_DialogTable = isnull(t.UID_DialogTable, tv.UID_DialogTable) AND xobj.ColumnName = 'XObjectKey'175  WHERE176    cef.IsInActive = 0 AND dbo.QBM_FGIConfigparmValue('QBM\CEF') = '1' AND tv.TableType IN('V', 'T') AND cef.OperationType = @OperationType AND177  tv.PKName1 > ' ' AND(xobj.ColumnName IS NOT NULL OR(isnull(t.isMNTable, tv.IsMNTable) = 0 AND isnull(t.UsageType,178  tv.UsageType) IN('USERDATA', 'HISTORY')))) AS x179  WHERE180    x.TableForTrigger = @TableForTrigger181  SELECT @ElementCount = @@ROWCOUNT182  SELECT @ElementIndex = 1183  WHILE @ElementIndex <= @ElementCount184  BEGIN185    SELECT @whereclause = @LeererAnfang186    SELECT187      TOP 1 @ColumnToCheck = e.ColumnToCheck,188      @TableToCheck = e.TableToCheck,189      @ColumnForTrigger = e.ColumnForTrigger,190      @SchemaDatatype = e.SchemaDatatype,191      @WhereClauseCustom = CASE trim(isnull(e.WhereClause, ''))192      WHEN '' THEN193      '1=1'194    ELSE e.WhereClause195    END,196    @Param01 = e.Param01,197    @Param02 = e.Param02,198    @Param03 = e.Param03,199    @Param04 = e.Param04,200    @Param05 = e.Param05,201    @UID_QBMCEFDefinition = e.UID_QBMCEFDefinition,202    @PKName1 = e.PKName1,203    @HasXObjectKey = e.HasXObjectKey204    FROM @elements e205    WHERE206      e.ElementIndex = @ElementIndex207    IF @IsForCheckExecutable = 1208    BEGIN209      SELECT210        @DeletedName = @TableForTrigger,211        @InsertedName = @TableForTrigger212    END213    IF @OperationType = 'U' AND @IsForCheckExecutable = 0214    BEGIN215      SELECT216        @erg = CONCAT(@erg,217        char(13),218        char(10),219        N 'if update(',220        @ColumnForTrigger,221        ')',222        char(13),223        char(10),224        ' begin',225        char(13),226        char(10))227    END228    SELECT @AliasOld = CASE @OperationType229    WHEN 'I' THEN230    'new'231    WHEN 'U' THEN232    'old'233    WHEN 'D' THEN234    'old'235    END236    SELECT @AliasNew = CASE @OperationType237    WHEN 'I' THEN238    'new'239    WHEN 'U' THEN240    'new'241    WHEN 'D' THEN242    'old'243    END244    SELECT245      @SelectParam01 = dbo.QBM_FSQCEFParmSelect(@TableToCheck,246      @Param01,247      @AliasNew,248      'fk01')249    SELECT250      @SelectParam02 = dbo.QBM_FSQCEFParmSelect(@TableToCheck,251      @Param02,252      @AliasNew,253      'fk02')254    SELECT255      @SelectParam03 = dbo.QBM_FSQCEFParmSelect(@TableToCheck,256      @Param03,257      @AliasNew,258      'fk03')259    SELECT260      @SelectParam04 = dbo.QBM_FSQCEFParmSelect(@TableToCheck,261      @Param04,262      @AliasNew,263      'fk04')264    SELECT265      @SelectParam05 = dbo.QBM_FSQCEFParmSelect(@TableToCheck,266      @Param05,267      @AliasNew,268      'fk05')269    SELECT270      @SelectList = CONCAT(@LeererAnfang,271      char(13),272      char(10),273      char(9),274      char(13),275      char(10),276    CASE @IsForCheckExecutable277      WHEN 1 THEN278    ' select newid() '279    ELSE 'select @UID_QBMCEFMessage' + dbo.QBM_FCVIntToString(@ElementIndex)280    END,281    ' as UID_QBMCEFMessage',282    char(13),283    char(10),284    ', ''',285    @Operation,286    ''' as Operation',287    char(13),288    char(10),289    ', ',290    dbo.QBM_FCVStringToIndent(@SelectParam01, 2),291    ' as ParamValue1 ',292    '/* ',293    @Param01,294    '*/',295    char(13),296    char(10),297    ', ',298    dbo.QBM_FCVStringToIndent(@SelectParam02, 2),299    ' as ParamValue2 ',300    '/* ',301    @Param02,302    '*/',303    char(13),304    char(10),305    ', ',306    dbo.QBM_FCVStringToIndent(@SelectParam03, 2),307    ' as ParamValue3 ',308    '/* ',309    @Param03,310    '*/',311    char(13),312    char(10),313    ', ',314    dbo.QBM_FCVStringToIndent(@SelectParam04, 2),315    ' as ParamValue4 ',316    '/* ',317    @Param04,318    '*/',319    char(13),320    char(10),321    ', ',322    dbo.QBM_FCVStringToIndent(@SelectParam05, 2),323    ' as ParamValue5 ',324    '/* ',325    @Param05,326    '*/')327    IF @HasXObjectKey = 1328    BEGIN329      SELECT330        @SelectList = CONCAT(@SelectList,331        char(13),332        char(10),333        ', ',334        @AliasNew,335        '.',336        'XObjectKey as ObjectKeyRelated')337    END338    ELSE339    BEGIN340      SELECT341        @SelectList = CONCAT(@SelectList,342        char(13),343        char(10),344        ', ',345        '''<Key><T>',346        @TableToCheck,347        '</T><P>'' + ',348        @AliasNew,349        '.',350        @PKName1,351        ' + ''</P></Key>'' as ObjectKeyRelated')352    END353    SELECT354      @SelectList = CONCAT(@SelectList,355      char(13),356      char(10),357      ', ''',358      @UID_QBMCEFDefinition,359      ''' as UID_QBMCEFDefinition ')360    SELECT361      @JoinList = CONCAT(@LeererAnfang,362      char(13),363      char(10),364      dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck, @Param01, @AliasNew, 'fk01'), 5),365      char(13),366      char(10),367      dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck, @Param02, @AliasNew, 'fk02'), 5),368      char(13),369      char(10),370      dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck, @Param03, @AliasNew, 'fk03'), 5),371      char(13),372      char(10),373      dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck, @Param04, @AliasNew, 'fk04'), 5),374      char(13),375      char(10),376      dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck, @Param05, @AliasNew, 'fk05'), 5))377    IF @Operation = 'Update'378    BEGIN379      SELECT380        @FromList = CONCAT(@LeererAnfang,381        char(9),382        N 'from ',383        @DeletedName,384        ' ',385        @AliasOld,386        ' join ',387        @TableForTrigger,388        N ' ',389        @AliasNew,390        ' on ',391        dbo.QBM_FSQTableJoin(@TableForTrigger, @AliasOld, @AliasNew))392      SELECT393        @WhereClause = CONCAT(@LeererAnfang,394        char(13),395        char(10),396        char(9),397        'where ' + dbo.QBM_FSQIsNullClauseCmp(@TableForTrigger, @ColumnForTrigger, @AliasOld),398        ' <> ',399        dbo.QBM_FSQIsNullClauseCmp(@TableForTrigger, @ColumnForTrigger, @AliasNew),400      CASE401        WHEN @SchemaDatatype LIKE '%char%' THEN402      ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ '403      ELSE ''404      END)405    END406    IF @Operation = 'Insert'407    BEGIN408      SELECT409        @FromList = CONCAT(N '',410        char(9),411        N 'from ',412        @InsertedName,413        ' ',414        @AliasNew,415        ' ')416      SELECT417        @WhereClause = CONCAT(char(13),418        char(10),419        char(9),420        'where 1=1')421    END422    IF @Operation = 'delete'423    BEGIN424      SELECT425        @FromList = CONCAT(char(9),426        N 'from ',427        @DeletedName,428        ' ',429        @AliasOld,430        ' ')431      SELECT432        @WhereClause = CONCAT(char(13),433        char(10),434        char(9),435        'where 1=1')436    END437    IF @TableForTrigger <> @TableToCheck AND @HasXObjectKey = 1438    BEGIN439      SELECT440        @WhereClause = CONCAT(@WhereClause,441        char(13),442        char(10),443        char(9),444        ' and ',445      CASE @Operation446        WHEN 'Update' THEN447      @AliasOld448      ELSE @AliasNew449      END,450      '.XObjectKey like ''<Key><T>',451      @TableToCheck,452      '</T>%',453      ''' ')454    END455    IF @WhereClause > ' ' AND @WhereClauseCustom > ' '456    BEGIN457      INSERT INTO @ColumnsToReplace(Parameter1,458      Parameter2)459      SELECT460        cv.ColumnName,461        cb.ColumnName462      FROM(463      SELECT464        tv.UID_DialogTable,465        tb.UID_DialogTable AS UID_DialogTableBasis,466        isnull(tb.UID_DialogTable, tv.UID_DialogTable) AS UID_DialogTableFinal467      FROM DialogTable tv468      LEFT469      OUTER470      JOIN Dialogtable tb471        ON tv.UID_DialogTableBase = tb.UID_DialogTable AND tv.TableName = @TableToCheck472      WHERE473        tv.TableName = @TableToCheck) tab474      JOIN DialogColumn cv475        ON cv.UID_DialogTable = tab.UID_DialogTable476      JOIN DialogColumn cb477        ON cb.UID_DialogTable = tab.UID_DialogTableFinal AND cv.UID_BaseColumn = cb.UID_DialogColumn478      WHERE479        tab.UID_DialogTable <> tab.UID_DialogTableFinal AND cv.ColumnName <> cb.ColumnName480      IF @@ROWCOUNT > 0481      BEGIN482        DELETE @Morpheme483        INSERT INTO @Morpheme(ContentFull)484        SELECT m.Morphem485        FROM dbo.QBM_FCVStringToListSQLMorphem0(@WhereClauseCustom,486        0,487        0) m488        UPDATE @Morpheme489        SET ContentFull = CONCAT('old.',490        cr.Parameter2)491        FROM @Morpheme m CROSS492        JOIN @ColumnsToReplace cr493        WHERE494          m.ContentFull = CONCAT('old.',495        cr.Parameter1)496        UPDATE @Morpheme497        SET ContentFull = CONCAT('new.',498        cr.Parameter2)499        FROM @Morpheme m CROSS500        JOIN @ColumnsToReplace cr501        WHERE502          m.ContentFull = CONCAT('new.',503        cr.Parameter1)504        SELECT505          TOP 1 @WhereClauseCustom = string_agg(m.ContentFull,506          ' ') within507          GROUP(508        ORDER BY m.elementIndex)509        FROM @Morpheme m510      END511      SELECT512        @WhereClause = CONCAT(@WhereClause,513        char(13),514        char(10),515        char(9),516        'and ( -- Custom Condition',517        char(13),518        char(10),519        dbo.QBM_FCVStringToIndent(@WhereClauseCustom, 3),520        char(13),521        char(10),522        char(9),523        char(9),524        ') -- / Custom Condition ')525    END526    SELECT527      @erg = CONCAT(@erg,528      char(13),529      char(10),530    CASE @IsForCheckExecutable531      WHEN 0 THEN532    'declare @UID_QBMCEFMessage' + dbo.QBM_FCVIntToString(@ElementIndex) + ' varchar(38) = newid()'533    ELSE ''534    END,535    dbo.QBM_FCVStringToIndent(@InsertList, 1),536    dbo.QBM_FCVStringToIndent(@SelectList, 3),537    char(13),538    char(10),539    dbo.QBM_FCVStringToIndent(@FromList, 2),540    @JoinList,541    dbo.QBM_FCVStringToIndent(@WhereClause, 3),542    char(13),543    char(10),544    char(9),545    char(9),546    ') as x',547    char(13),548    char(10),549    char(13),550    char(10))551    IF @SelectParam01 LIKE '%<temporarily ParameterDefinition>%' OR @SelectParam01 LIKE '%$Script(%)$%'552    BEGIN553      SELECT @ColListTemplate = 'ParamValue01'554    END555    IF @SelectParam02 LIKE '%<temporarily ParameterDefinition>%' OR @SelectParam02 LIKE '%$Script(%)$%'556    BEGIN557      IF @ColListTemplate > ' '558      BEGIN559        SELECT560          @ColListTemplate = CONCAT(@ColListTemplate,561          '|')562      END563      SELECT564        @ColListTemplate = CONCAT(@ColListTemplate,565        'ParamValue02')566    END567    IF @SelectParam03 LIKE '%<temporarily ParameterDefinition>%' OR @SelectParam03 LIKE '%$Script(%)$%'568    BEGIN569      IF @ColListTemplate > ' '570      BEGIN571        SELECT572          @ColListTemplate = CONCAT(@ColListTemplate,573          '|')574      END575      SELECT576        @ColListTemplate = CONCAT(@ColListTemplate,577        'ParamValue03')578    END579    IF @SelectParam04 LIKE '%<temporarily ParameterDefinition>%' OR @SelectParam04 LIKE '%$Script(%)$%'580    BEGIN581      IF @ColListTemplate > ' '582      BEGIN583        SELECT584          @ColListTemplate = CONCAT(@ColListTemplate,585          '|')586      END587      SELECT588        @ColListTemplate = CONCAT(@ColListTemplate,589        'ParamValue04')590    END591    IF @SelectParam05 LIKE '%<temporarily ParameterDefinition>%' OR @SelectParam05 LIKE '%$Script(%)$%'592    BEGIN593      IF @ColListTemplate > ' '594      BEGIN595        SELECT596          @ColListTemplate = CONCAT(@ColListTemplate,597          '|')598      END599      SELECT600        @ColListTemplate = CONCAT(@ColListTemplate,601        'ParamValue05')602    END603    IF @ColListTemplate > ' ' AND @IsForCheckExecutable = 0604    BEGIN605      SELECT606        @erg = CONCAT(@erg,607        char(13),608        char(10),609        'declare @WhereTemplate',610        dbo.QBM_FCVIntToString(@ElementIndex),611        ' nvarchar(1024)',612        char(13),613        char(10),614        'select @WhereTemplate',615        dbo.QBM_FCVIntToString(@ElementIndex),616        ' = ''UID_QBMCEFMessage = '''''' + @UID_QBMCEFMessage',617        dbo.QBM_FCVIntToString(@ElementIndex),618        ' + ''''''''',619        char(13),620        char(10),621        'exec QBM_PJobCreate_HOTemplate_B ''QBMCEFMessage'', @WhereTemplate',622        dbo.QBM_FCVIntToString(@ElementIndex),623        ', ''',624        @ColListTemplate,625        '''626																		, @GenProcID627																		, @AdditionalObjectKeysAffected = default628																		, @priority = 10629										'630        ,631        char(13),632        char(10),633        char(13),634        char(10))635    END636    IF @Operation = 'update' AND @IsForCheckExecutable = 0637    BEGIN638      SELECT639        @erg = CONCAT(@erg,640        char(13),641        char(10),642        N ' end -- if update(',643        @ColumnForTrigger,644        ')')645    END646    SELECT @ElementIndex += 1647  END648  IF @erg > ' '649  BEGIN650    SELECT @erg = CONCAT(CASE @IsForCheckExecutable651    WHEN 1 THEN652    ' select top 1 1 as spalte'653    ELSE ''654    END,655    '656----------------------------------------------------------------657-- information for CEF, Operation =  ',658    @operation,659    '660----------------------------------------------------------------661662	',663    @erg,664    '665----------------------------------------------------------------666-- / information for CEF, Operation = ',667    @operation,668    '669----------------------------------------------------------------670671')672  END673  RETURN(@erg)674END
Open raw exported source
SQL ยท Raw127 lines
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) declare5 @WhereClauseCustom nvarchar(max) declare @TableToCheck varchar(30) declare @UID_QBMCEFDefinition varchar(38) declare @PKName1 varchar(30) declare @HasXObjectKey6 bit declare @WhereClause nvarchar(max) declare @erg nvarchar(max) = N'' declare @SchemaDatatype varchar(128) declare @AliasOld varchar(16) = 'old' declare7 @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 NULL9 , ColumnToCheck varchar(30) collate database_default  , TableToCheck varchar(30) collate database_default  , ColumnForTrigger varchar(30) collate database_default10  , SchemaDatatype varchar(128) collate database_default , WhereClause nvarchar(max) collate database_default , Param01 nvarchar(1024) collate database_default11 NULL , Param02 nvarchar(1024) collate database_default NULL , Param03 nvarchar(1024) collate database_default NULL , Param04 nvarchar(1024) collate database_default12 NULL , Param05 nvarchar(1024) collate database_default NULL , UID_QBMCEFDefinition varchar(38) , PKName1 varchar(30) collate database_default , HasXObjectKey13 bit default 0 ) declare @ElementCount int declare @ElementIndex int declare @InsertList nvarchar(max) = N'' declare @ColumnsToReplace QBM_YParameterList14   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_QBMCEFDefinition20							, 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 @IsForCheckExecutable28 when 1 then ' top 1 1 as spalte' else '29		 x.UID_QBMCEFMessage 30		, left(upper(x.Operation),1) as Operation -- I U D31		, x.ParamValue1 , x.ParamValue2 , x.ParamValue3 , x.ParamValue4 , x.ParamValue5 	32		, x.ObjectKeyRelated33		, x.UID_QBMCEFDefinition34		, dbo.QBM_FCVElementToObjectKey1(''QBMCEFMessage'', ''UID_QBMCEFMessage'', x.UID_QBMCEFMessage) as XObjectKey35		, @Xdate as MessageDate36		, @ActionUser as LogonUser37		'38 end , '39 from ( ' )   declare @DeletedName varchar(30) = 'deleted' declare @InsertedName varchar(30) = 'inserted' declare @SelectList nvarchar(max) declare40 @FromList nvarchar(max) declare @JoinList nvarchar(max) insert into @elements(ColumnToCheck, TableToCheck , ColumnForTrigger , SchemaDatatype, WhereClause41 , Param01 , Param02 , Param03, Param04 , Param05 , UID_QBMCEFDefinition , PKName1, HasXObjectKey ) select x.ColumnToCheck, x.TableToCheck, x.ColumnForTrigger42, 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.ColumnName44) as ColumnForTrigger  , cb.SchemaDataType, cef.WhereClause , cef.Param01 , cef.Param02 , cef.Param03, cef.Param04 , cef.Param05 , cef.UID_QBMCEFDefinition45 , isnull(t.PKName1, tv.PKName1) as PKName1 , case when xobj.ColumnName is null then 0 else 1 end as HasXObjectKey from QBMCEFDefinition cef join DialogTable46 tv on cef.UID_DialogTable = tv.UID_DialogTable left outer join DialogTable t on tv.UID_DialogTableBase = t.UID_DialogTable left outer join DialogColumn47 cv on cef.UID_DialogColumn = cv.UID_DialogColumn left outer join DialogColumn cb on cv.UID_BaseColumn = cb.UID_DialogColumn left outer join DialogColumn48 xobj on xobj.UID_DialogTable = isnull(t.UID_DialogTable, tv.UID_DialogTable) and xobj.ColumnName = 'XObjectKey' where cef.IsInActive = 0 and dbo.QBM_FGIConfigparmValue49('QBM\CEF') = '1' and tv.TableType in ('V', 'T') and cef.OperationType = @OperationType and tv.PKName1 > ' ' and (  xobj.ColumnName is not null or  (isnull50(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 , @WhereClauseCustom53 = case trim(isnull(e.WhereClause,'')) when '' then '1=1' else e.WhereClause end , @Param01 = e.Param01, @Param02 = e.Param02, @Param03 = e.Param03, @Param0454 = e.Param04, @Param05 = e.Param05 , @UID_QBMCEFDefinition = e.UID_QBMCEFDefinition , @PKName1 = e.PKName1 , @HasXObjectKey = e.HasXObjectKey from @elements55 e where e.ElementIndex = @ElementIndex if @IsForCheckExecutable = 1 begin select @DeletedName = @TableForTrigger , @InsertedName = @TableForTrigger end56 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' then58 'old' end select @AliasNew = case @OperationType when 'I' then 'new' when 'U' then 'new' when 'D' then 'old' end select @SelectParam01 = dbo.QBM_FSQCEFParmSelect59(@TableToCheck , @Param01 , @AliasNew , 'fk01' ) select @SelectParam02 = dbo.QBM_FSQCEFParmSelect(@TableToCheck , @Param02 , @AliasNew , 'fk02' ) select60 @SelectParam03 = dbo.QBM_FSQCEFParmSelect(@TableToCheck , @Param03 , @AliasNew , 'fk03' ) select @SelectParam04 = dbo.QBM_FSQCEFParmSelect(@TableToCheck61 , @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(1064), ', ', dbo.QBM_FCVStringToIndent(@SelectParam01, 2), ' as ParamValue1 ', '/* ', @Param01 , '*/' , char(13), char(10), ', ', dbo.QBM_FCVStringToIndent65(@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(1367), char(10), ', ', dbo.QBM_FCVStringToIndent(@SelectParam05, 2), ' as ParamValue5 ', '/* ', @Param05 , '*/' ) if @HasXObjectKey = 1 begin select @SelectList68 = concat(@SelectList, char(13), char(10), ', ', @AliasNew , '.' , 'XObjectKey as ObjectKeyRelated') end else begin select @SelectList = concat(@SelectList69, char(13), char(10), ', ', '''<Key><T>', @TableToCheck, '</T><P>'' + ', @AliasNew, '.', @PKName1, ' + ''</P></Key>'' as ObjectKeyRelated') end  select70 @SelectList = concat(@SelectList, char(13), char(10), ', ''', @UID_QBMCEFDefinition, ''' as UID_QBMCEFDefinition ' )  select @JoinList = concat(@LeererAnfang71, char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param01 , @AliasNew , 'fk01' ),5) , char(13), char(10), dbo.QBM_FCVStringToIndent72(dbo.QBM_FSQCEFJoin(@TableToCheck , @Param02 , @AliasNew , 'fk02' ),5) , char(13), char(10), dbo.QBM_FCVStringToIndent(dbo.QBM_FSQCEFJoin(@TableToCheck73 , @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, @AliasNew78) , case when @SchemaDatatype like '%char%' then ' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/ ' else '' end )   end if @Operation = 'Insert' begin79 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( char81(13), char(10), char(9), 'where 1=1' )  end if @TableForTrigger <> @TableToCheck and @HasXObjectKey = 1 begin select @WhereClause = concat( @WhereClause82, char(13), char(10), char(9) , ' and ' , case @Operation when 'Update' then @AliasOld else @AliasNew end ,'.XObjectKey like ''<Key><T>', @TableToCheck83, '</T>%', ''' ' )  end if @WhereClause > ' ' and @WhereClauseCustom > ' ' begin  insert into @ColumnsToReplace(Parameter1, Parameter2) select cv.ColumnName84, cb.ColumnName from ( select tv.UID_DialogTable, tb.UID_DialogTable as UID_DialogTableBasis, isnull(tb.UID_DialogTable, tv.UID_DialogTable) as UID_DialogTableFinal85 from DialogTable tv left outer join Dialogtable tb on tv.UID_DialogTableBase = tb.UID_DialogTable and tv.TableName = @TableToCheck where tv.TableName 86= @TableToCheck ) tab join DialogColumn cv on cv.UID_DialogTable = tab.UID_DialogTable join DialogColumn cb on cb.UID_DialogTable = tab.UID_DialogTableFinal87 and cv.UID_BaseColumn = cb.UID_DialogColumn where tab.UID_DialogTable <> tab.UID_DialogTableFinal and cv.ColumnName <> cb.ColumnName if @@ROWCOUNT > 088 begin delete @Morpheme insert into @Morpheme(ContentFull) select m.Morphem from dbo.QBM_FCVStringToListSQLMorphem0(@WhereClauseCustom,0 ,0) m update @Morpheme89 set ContentFull = concat('old.', cr.Parameter2) from @Morpheme m cross join @ColumnsToReplace cr where m.ContentFull = concat('old.', cr.Parameter1) update90 @Morpheme set ContentFull = concat('new.', cr.Parameter2) from @Morpheme m cross join @ColumnsToReplace cr where m.ContentFull = concat('new.', cr.Parameter191) select top 1 @WhereClauseCustom = string_agg (m.ContentFull, ' ' ) within group(order by m.elementIndex) from @Morpheme m end  select @WhereClause = 92concat(@WhereClause, char(13), char(10), char(9), 'and ( -- Custom Condition' , char(13), char(10) , dbo.QBM_FCVStringToIndent(@WhereClauseCustom, 3)  93, char(13), char(10), char(9), char(9), ') -- / Custom Condition ' ) end select @erg = concat(@erg , char(13), char(10) , case @IsForCheckExecutable when94 0 then 'declare @UID_QBMCEFMessage' + dbo.QBM_FCVIntToString(@ElementIndex) + ' varchar(38) = newid()' else '' end , dbo.QBM_FCVStringToIndent (@InsertList95, 1) , dbo.QBM_FCVStringToIndent (@SelectList, 3) , char(13), char(10) , dbo.QBM_FCVStringToIndent (@FromList, 2) , @JoinList , dbo.QBM_FCVStringToIndent96(@WhereClause, 3) , char(13), char(10), char(9),char(9), ') as x' , char(13), char(10) , char(13), char(10) ) if @SelectParam01 like '%<temporarily ParameterDefinition>%'97 or @SelectParam01 like '%$Script(%)$%' begin select @ColListTemplate = 'ParamValue01' end if @SelectParam02 like '%<temporarily ParameterDefinition>%'98 or @SelectParam02 like '%$Script(%)$%' begin if @ColListTemplate > ' ' begin select @ColListTemplate = concat(@ColListTemplate, '|') end select @ColListTemplate99 = concat(@ColListTemplate, 'ParamValue02') end if @SelectParam03 like '%<temporarily ParameterDefinition>%' or @SelectParam03 like '%$Script(%)$%' begin100 if @ColListTemplate > ' ' begin select @ColListTemplate = concat(@ColListTemplate, '|') end select @ColListTemplate = concat(@ColListTemplate, 'ParamValue03'101) end if @SelectParam04 like '%<temporarily ParameterDefinition>%' or @SelectParam04 like '%$Script(%)$%' begin if @ColListTemplate > ' ' begin select 102@ColListTemplate = concat(@ColListTemplate, '|') end select @ColListTemplate = concat(@ColListTemplate, 'ParamValue04') end if @SelectParam05 like '%<temporarily ParameterDefinition>%'103 or @SelectParam05 like '%$Script(%)$%' begin if @ColListTemplate > ' ' begin select @ColListTemplate = concat(@ColListTemplate, '|') end select @ColListTemplate104 = concat(@ColListTemplate, 'ParamValue05') end if @ColListTemplate > ' ' and @IsForCheckExecutable = 0  begin select @erg = concat(@erg , char(13), char105(10) , 'declare @WhereTemplate', dbo.QBM_FCVIntToString(@ElementIndex), ' nvarchar(1024)' , char(13), char(10) , 'select @WhereTemplate', dbo.QBM_FCVIntToString106(@ElementIndex), ' = ''UID_QBMCEFMessage = '''''' + @UID_QBMCEFMessage', dbo.QBM_FCVIntToString(@ElementIndex), ' + ''''''''' , char(13), char(10) , 'exec QBM_PJobCreate_HOTemplate_B ''QBMCEFMessage'', @WhereTemplate'107, dbo.QBM_FCVIntToString(@ElementIndex), ', ''', @ColListTemplate, '''108																		, @GenProcID109																		, @AdditionalObjectKeysAffected = default110																		, @priority = 10111										'112 , char(13), char(10) , char(13), char(10) ) end if @Operation = 'update' and @IsForCheckExecutable = 0 begin select @erg = concat(@erg ,char(13), char113(10), N' end -- if update(' , @ColumnForTrigger , ')' )  end select @ElementIndex += 1 end  if @erg > ' ' begin select @erg = concat( case @IsForCheckExecutable114 when 1 then ' select top 1 1 as spalte' else '' end, '115----------------------------------------------------------------116-- information for CEF, Operation =  '117, @operation,'118----------------------------------------------------------------119120	', @erg, '121----------------------------------------------------------------122-- / information for CEF, Operation = '123, @operation,'124----------------------------------------------------------------125126' ) end  return (@erg) end 127