dbo.QER_ZDynamicGroupMakeTrigger

1CREATE PROCEDURE QER_ZDynamicGroupMakeTrigger(2  @SlotNumberDummy int,3  @parm1dummy varchar(38),4  @parm2dummy varchar(38),5  @GenProcIDdummy varchar(38)6)7AS8BEGIN9  DECLARE @crlf varchar(2) = char(13) + char(10)10  DECLARE @ElementCount int11  DECLARE @ElementIndex int12  DECLARE @TableName varchar(30)13  DECLARE @Body nvarchar(max)14  DECLARE @Triggername varchar(30)15  DECLARE @SQLCmd nvarchar(max)16  DECLARE @DebugSwitch int = 017  DECLARE @Material TABLE(ElementIndex int identity primary key,18  TableName varchar(30) collate database_default,19  TriggerInsert nvarchar(max) collate database_default,20  TriggerUpdate nvarchar(max) collate database_default,21  TriggerDelete nvarchar(max) collate database_default)22  BEGIN TRY23    EXEC QBM_PTriggerDrop 'GEN_T[I,U,D]DYG%'24    INSERT INTO @Material(TableName,25    TriggerInsert,26    TriggerUpdate,27    TriggerDelete)28    SELECT29      z.TableName,30      STRING_AGG(replace(z.WatchInsertDelete, '%opSynonym%', 'inserted'),31      @crlf) within32      GROUP(33    ORDER BY z.Columnname),34    STRING_AGG(z.Watchupdate,35    @crlf) within36    GROUP(37    ORDER BY z.Columnname),38    STRING_AGG(replace(z.WatchInsertDelete, '%opSynonym%', 'deleted'),39    @crlf) within40    GROUP(41    ORDER BY z.Columnname)42    FROM(43    SELECT CASE44    WHEN y.UID_DialogTable <> y.UID_DialogTableObjectClass AND y.IsPKMember = 0 AND y.JoinColumns > ' ' THEN45    CONCAT(convert(nvarchar(max), '--	detail with join'), @crlf, 'if update(', y.ColumnName, ')', @crlf, ' begin',46    @crlf, '	insert into @ElementAndRule(Parameter1, Parameter2)', @crlf, '	select ot.', oc.PKName1, ' , ''',47    y.UID_DynamicGroup, ''' --', dyg.DisplayName, @crlf, '	from ', oc.TableName, ' ot join inserted wt on ot.',48    oc.PKName1, ' in (', y.JoinColumns, ' )', @crlf, '	 union all ', @crlf, '	select ot.', oc.PKName1, ' , ''',49    y.UID_DynamicGroup, '''', @crlf, '	from ', oc.TableName, ' ot join deleted wt on ot.', oc.PKName1, ' in (',50    y.JoinColumns, ' )', @crlf, ' end', @crlf)51    WHEN y.UID_DialogTable <> y.UID_DialogTableObjectClass AND y.IsPKMember = 0 AND y.JoinColumns IS NULL THEN52    CONCAT(convert(nvarchar(max), '--	full'), @crlf, 'if update(', y.ColumnName, ')', @crlf, ' begin', @crlf,53    '	insert into @RuleOnly(Parameter1) select  ''', y.UID_DynamicGroup, ''' --', dyg.DisplayName, @crlf,54    ' end', @crlf)55    WHEN y.UID_DialogTable <> y.UID_DialogTableObjectClass AND y.IsPKMember = 1 THEN56    NULL57    WHEN y.UID_DialogTable = y.UID_DialogTableObjectClass AND y.IsPKMember = 0 THEN58    CONCAT(convert(nvarchar(max), '--	detail without join'), @crlf, 'if update(', y.ColumnName, ')', @crlf,59    ' begin', @crlf, '	insert into @ElementAndRule(Parameter1, Parameter2) ', @crlf, '	select wt.', oc.PKName1,60    ' , ''', y.UID_DynamicGroup, ''' --', dyg.DisplayName, @crlf, '	from inserted wt ', @crlf, '	 union all ',61    @crlf, '	select wt.', oc.PKName1, ' , ''', y.UID_DynamicGroup, '''', @crlf, '	from deleted wt ', @crlf,62    ' end', @crlf)63    ELSE NULL64    END AS WatchUpdate,65    CASE66      WHEN y.UID_DialogTable <> y.UID_DialogTableObjectClass AND y.JoinColumns > ' ' THEN67    CONCAT(convert(nvarchar(max), '--	detail with join'), @crlf, 'insert into @ElementAndRule(Parameter1, Parameter2) select ot.',68    oc.PKName1, ' , ''', y.UID_DynamicGroup, ''' --', dyg.DisplayName, @crlf, ' from ', oc.TableName, ' ot join %opSynonym% wt on ot.',69    oc.PKName1, ' in (', y.JoinColumns, ' )', @crlf)70      WHEN y.UID_DialogTable <> y.UID_DialogTableObjectClass AND y.JoinColumns IS NULL THEN71    CONCAT(convert(nvarchar(max), '--	full'), @crlf, 'insert into @RuleOnly(Parameter1) select  ''', y.UID_DynamicGroup,72    ''' --', dyg.DisplayName, @crlf)73      WHEN y.UID_DialogTable = y.UID_DialogTableObjectClass THEN74    CONCAT(convert(nvarchar(max), '--	detail myself'), @crlf, 'insert into @ElementAndRule(Parameter1, Parameter2) select wt.',75    oc.PKName1, ' , ''', y.UID_DynamicGroup, ''' --', dyg.DisplayName, @crlf, ' from %opSynonym% wt ', @crlf)76    END AS WatchInsertDelete,77    y.*78    FROM(79    SELECT80      x.*, STRING_AGG('wt.' + r.ChildColumn, ',') AS JoinColumns81    FROM(82    SELECT83      dg.UID_DynamicGroup, dg.UID_DialogTableObjectClass, isnull(tb.TableName, t.TableName) AS TableName,84      isnull(cb.ColumnName, c.ColumnName) AS ColumnName, isnull(tb.UID_DialogTable, t.UID_DialogTable) AS UID_DialogTable,85      c.IsPKMember86    FROM DynamicGroupHasImmediateColumn gc87    JOIN DynamicGroup dg88      ON gc.UID_DynamicGroup = dg.UID_DynamicGroup89    JOIN DialogColumn c90      ON gc.ObjectKeyDialogColumn = c.XObjectKey91    JOIN DialogTable t92      WITH(readpast)93      ON c.UID_DialogTable = t.UID_DialogTable94    LEFT95    OUTER96    JOIN DialogColumn cb97      ON c.UID_BaseColumn = cb.UID_DialogColumn98    LEFT99    OUTER100    JOIN DialogTable tb101      WITH(readpast)102      ON t.UID_DialogTableBase = tb.UID_DialogTable103    WHERE104      dg.IsCalculateImmediately = 1 AND gc.IsInActive = 0 AND t.TableType NOT IN('P', 'U', 'R') AND(dbo.QBM_FGIConfigparmValue('QER\Structures\DynamicGroupCheck\CalculateImmediately'105    + dbo.QBM_FGITableName(dg.UID_DialogTableObjectClass)) = '1' OR dbo.QBM_FCVGUIDToModuleOwner(dg.UID_DynamicGroup) <> 'CCC')) AS x106    LEFT107    OUTER108    JOIN QBM_VQBMRelation r109      ON r.UID_DialogTableParent <> x.UID_DialogTable AND r.UID_DialogTableParent = x.UID_DialogTableObjectClass AND r.UID_DialogTableChild = x.UID_DialogTable110    GROUP BY x.UID_DynamicGroup, x.UID_DialogTableObjectClass, x.TableName, x.ColumnName, x.UID_DialogTable,111    x.IsPKMember) AS y112    JOIN DialogTable oc113      WITH(readpast)114      ON y.UID_DialogTableObjectClass = oc.UID_DialogTable115    JOIN DynamicGroup dyg116      ON y.UID_DynamicGroup = dyg.UID_DynamicGroup) AS z117    GROUP BY z.TableName118    SELECT @ElementCount = @@ROWCOUNT119    DECLARE @Vars nvarchar(max) = '	120	declare @RuleOnly QBM_YParameterList	-- Parameter1 die UID_DynamicGroup121	declare @ElementAndRule QBM_YParameterList	-- Parameter1 UIDPerson(Hardware / Workdesk)122												-- Parameter2 die UID_DynamicGroup123124'125    DECLARE @PostProcessing nvarchar(max) = '	126127	-- postprocessing for all128129	if exists (select top 1 1130				from @ElementAndRule131			)132	 begin133		declare @DBQueueElements_detail QBM_YDBQueueRaw134	135		insert into @DBQueueElements_detail (Object, SubObject, GenProcID)136		select distinct x.Parameter1, x.Parameter2, @GenProcid137		from @ElementAndRule x138139		if @@ROWCOUNT < 10000140		 begin141			exec QBM_PDBQueueInsert_Bulk ''QER-K-DynamicGroupSingleBasic'', @DBQueueElements_detail142		 end143		else144		 begin145			insert into @RuleOnly(Parameter1)146			select distinct x.Parameter2147			from @ElementAndRule x148		 end149	 end150151	if exists (select top 1 1152				from @RuleOnly153			)154	 begin155		declare @DBQueueElements_full QBM_YDBQueueRaw156	157		insert into @DBQueueElements_full (Object, SubObject, GenProcID)158		select distinct x.Parameter1, null, @GenProcid159		from @RuleOnly x160161		exec QBM_PDBQueueInsert_Bulk ''QER-K-DynamicGroupTest'', @DBQueueElements_full162	 end163'164    SELECT @ElementIndex = 1165    WHILE @ElementIndex <= @ElementCount166    BEGIN167      SELECT168        TOP 1 @TableName = bu.TableName,169        @Body = bu.TriggerInsert,170        @Triggername = dbo.QBM_FGICodeName('TIDYG',171        bu.TableName)172      FROM @Material bu173      WHERE174        bu.ElementIndex = @ElementIndex175      IF @Body > ' '176      BEGIN177        SELECT178          @SQLCmd = dbo.QBM_FSQTriggerDef(@Triggername,179          @TableName,180          'Insert',181          CONCAT(@Body, @PostProcessing),182          @Vars,183          'DynamicGroup immediate for ' + @TableName + ' Insert')184        IF @DebugSwitch > 0185        BEGIN186          print @SQLCmd187        END188        EXEC QBM_PSQLCreate @TriggerName,189        'T',190          @SQLCmd,191        0,192        0193      END194      SELECT195        TOP 1 @Body = bu.TriggerUpdate,196        @Triggername = dbo.QBM_FGICodeName('TUDYG',197        bu.TableName)198      FROM @Material bu199      WHERE200        bu.ElementIndex = @ElementIndex201      IF @Body > ' '202      BEGIN203        SELECT204          @SQLCmd = dbo.QBM_FSQTriggerDef(@Triggername,205          @TableName,206          'update',207          CONCAT(@Body, @PostProcessing),208          @Vars,209          'DynamicGroup immediate for ' + @TableName + ' Update')210        IF @DebugSwitch > 0211        BEGIN212          print @SQLCmd213        END214        EXEC QBM_PSQLCreate @TriggerName,215        'T',216          @SQLCmd,217        0,218        0219      END220      SELECT221        TOP 1 @Body = bu.TriggerDelete,222        @Triggername = dbo.QBM_FGICodeName('TDDYG',223        bu.TableName)224      FROM @Material bu225      WHERE226        bu.ElementIndex = @ElementIndex227      IF @Body > ' '228      BEGIN229        SELECT230          @SQLCmd = dbo.QBM_FSQTriggerDef(@Triggername,231          @TableName,232          'delete',233          CONCAT(@Body, @PostProcessing),234          @Vars,235          'DynamicGroup immediate for ' + @TableName + ' delete')236        IF @DebugSwitch > 0237        BEGIN238          print @SQLCmd239        END240        EXEC QBM_PSQLCreate @TriggerName,241        'T',242          @SQLCmd,243        0,244        0245      END246      SELECT @ElementIndex += 1247    END248  END TRY249  BEGIN CATCH250    EXEC QBM_PSessionErrorAdd DEFAULT251    RAISERROR('',252    18,253    1)254      WITH NOWAIT255  END CATCH256  ende:257  RETURN258END