Back to OIM Explorer

dbo.QBM_FSQFKCheckDynamic

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 11.093 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGIColumnExists source text reference
references source dbo.QBM_FGIColumnExistsInSchema source text reference

References

Referenced By

dbo.QBM_PRITriggerCreate

Complete Source

1CREATE FUNCTION dbo.QBM_FSQFKCheckDynamic(2  @TableName varchar(30),3  @Operation varchar(30)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @erg nvarchar(max) = ''9  DECLARE @ergPart nvarchar(max) = ''10  DECLARE @Childtable varchar(30)11  DECLARE @ChildColumn varchar(30)12  DECLARE @ParentTable varchar(30)13  DECLARE @ChildHasXOrigin BIT14  DECLARE @ChildHasXIsInEffect BIT15  DECLARE @ParentHasXOrigin BIT = dbo.QBM_FGIColumnExists(@TableName,16  'XOrigin')17  DECLARE @ChildColumnName varchar(50)18  DECLARE @ParentList nvarchar(max) = ''19  DECLARE @work TABLE(parenttable varchar(30) collate database_default,20  childtable varchar(30) collate database_default,21  childColumn varchar(30) collate database_default,22  SortOrder int identity,23  ChildHasXOrigin BIT DEFAULT 0,24  ChildHasXIsInEffect BIT DEFAULT 0)25  DECLARE @AccessIndex int26  DECLARE @CannotInsertString nvarchar(max)27  IF @Operation = 'Insert'28  BEGIN29    SELECT30      @CannotInsertString = '#LDS#Cannot insert object in {2} because the associated object in {1} does not exist. Rule {0}. Invalid Value is {3}.|'31  END32  ELSE33  BEGIN34    SELECT35      @CannotInsertString = '#LDS#Cannot update object in {2} because the associated object in {1} does not exist. Rule {0}. Invalid Value is {3}.|'36  END37  DELETE @work38  INSERT INTO @work(parenttable,39  childtable,40  childColumn,41  ChildHasXOrigin,42  ChildHasXIsInEffect)43  SELECT44    @TableName AS ParentTable,45    ct.TableName AS ChildTable,46    c.ColumnName AS ChildColumn,47    sign(LEN(ISNULL(co.UID_DialogColumn, ''))),48    sign(LEN(ISNULL(ce.UID_DialogColumn, '')))49  FROM QBM_VDialogValidDynamicRef_Exp dr50  JOIN DialogColumn c51    WITH(readpast)52    ON dr.UID_DialogColumn = c.UID_DialogColumn53  JOIN DialogTable ct54    WITH(readpast)55    ON c.UID_DialogTable = ct.UID_DialogTable56  JOIN DialogTable t57    WITH(readpast)58    ON dr.UID_DialogTableReference = t.UID_DialogTable59  LEFT60  OUTER61  JOIN DialogColumn co62    WITH(readpast)63    ON co.UID_DialogTable = ct.UID_DialogTable AND co.ColumnName = 'XOrigin'64  LEFT65  OUTER66  JOIN DialogColumn ce67    WITH(readpast)68    ON ce.UID_DialogTable = ct.UID_DialogTable AND ce.ColumnName = 'XIsInEffect'69  WHERE70    t.TableName = @TableName AND dr.parentExecuteBy = 'T' AND dr.ParentRestriction = 'DC' AND(@Operation = 'delete' AND @ParentHasXOrigin =71  0 OR @Operation = 'update' AND @ParentHasXOrigin = 1)72  WHILE 0 <(73  SELECT COUNT(*)74  FROM @work)75  BEGIN76    SELECT77      TOP 1 @AccessIndex = w.SortOrder,78      @ParentTable = w.parenttable,79      @Childtable = w.childtable,80      @ChildColumn = w.childColumn,81      @ChildHasXOrigin = w.ChildHasXOrigin,82      @ChildHasXIsInEffect = w.ChildHasXIsInEffect,83      @ChildColumnName = CASE84      WHEN isnull(w.childColumn,85      '') = '' THEN86      ''87    ELSE CONCAT('(',88    LEFT(w.childColumn, 64),89    ')')90    END91    FROM @work w92    ORDER BY w.SortOrder DESC93    SELECT94      @erg = @erg + '95-- Dynamic FK  for Parent ' + @ParentTable + ' to Child ' + @childTable + @ChildColumnName + ' ON PARENT DELETE CASCADE 96	 if exists( select top 1 1 97				from deleted d join '98      + @childTable + ' x on d.XObjectKey = x.' + @childColumn + '99		 ' + CASE @ParentHasXOrigin100      WHEN 1 THEN101      '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'102    ELSE ''103    END + '104			  )105	  begin	'106    IF @ChildHasXOrigin = 1107    BEGIN108      SELECT109        @erg = @erg + '	  --  (modify XOrigin)110			update ' + @childTable + '111			 set XOrigin = 0'112      IF @ChildHasXIsInEffect = 1113      BEGIN114        SELECT115          @erg = @erg + ', XIsInEffect = 0'116      END117      IF dbo.QBM_FGIColumnExistsInSchema(@childTable,118      'XDateUpdated') = 1119      BEGIN120        SELECT121          @erg = @erg + ', XDateUpdated = @Xdate, XUserUpdated = @XUser'122      END123    END124    ELSE125    BEGIN126      SELECT @erg = @erg + '	  127			delete ' + @childTable128    END129    SELECT130      @erg = @erg + '131		 from ' + @childTable + ' join deleted d on d.XObjectKey = ' + @childTable + '.' + @childColumn + '132		 ' + CASE @ParentHasXOrigin133      WHEN 1 THEN134      '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'135    ELSE ''136    END + '137	  end		138	'139    DELETE @work140    WHERE141      SortOrder = @AccessIndex142  END143  DELETE @work144  INSERT INTO @work(parenttable,145  childtable,146  childColumn)147  SELECT148    @TableName AS ParentTable,149    ct.TableName AS ChildTable,150    c.ColumnName AS ChildColumn151  FROM QBM_VDialogValidDynamicRef_Exp dr152  JOIN DialogColumn c153    WITH(readpast)154    ON dr.UID_DialogColumn = c.UID_DialogColumn155  JOIN DialogTable ct156    WITH(readpast)157    ON c.UID_DialogTable = ct.UID_DialogTable158  JOIN DialogTable t159    WITH(readpast)160    ON dr.UID_DialogTableReference = t.UID_DialogTable161  WHERE162    t.TableName = @TableName AND dr.parentExecuteBy = 'T' AND dr.ParentRestriction = 'DS' AND(@Operation = 'delete' AND @ParentHasXOrigin =163  0 OR @Operation = 'update' AND @ParentHasXOrigin = 1)164  WHILE 0 <(165  SELECT COUNT(*)166  FROM @work)167  BEGIN168    SELECT169      TOP 1 @AccessIndex = w.SortOrder,170      @ParentTable = w.parenttable,171      @Childtable = w.childtable,172      @ChildColumn = w.childColumn,173      @ChildColumnName = CASE174      WHEN isnull(w.childColumn,175      '') = '' THEN176      ''177    ELSE CONCAT('(',178    LEFT(w.childColumn, 64),179    ')')180    END181    FROM @work w182    ORDER BY w.SortOrder DESC183    SELECT184      @erg = @erg + '185-- Dynamic FK  for Parent ' + @ParentTable + ' to Child ' + @childTable + @ChildColumnName + ' ON PARENT DELETE SET NULL 186	 if exists( select top 1 1 187				from deleted d join '188      + @childTable + ' x on d.XObjectKey = x.' + @childColumn + '189		 ' + CASE @ParentHasXOrigin190      WHEN 1 THEN191      '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'192    ELSE ''193    END + '194			  )195	  begin		  196		update ' + @childTable + '197			set ' + @childColumn + ' = null ' + CASE198    WHEN dbo.QBM_FGIColumnExistsInSchema(@Childtable,199    'XDateUpdated') = 1 AND @ChildColumn NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN200    ', XDateUpdated = @Xdate, XUserUpdated = @XUser'201    ELSE ''202    END + '203		 from ' + @childTable + ' join deleted d on d.XObjectKey = ' + @childTable + '.' + @childColumn + '204		 ' + CASE @ParentHasXOrigin205    WHEN 1 THEN206    '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'207    ELSE ''208    END + '209	  end		210	'211    DELETE @work212    WHERE213      SortOrder = @AccessIndex214  END215  DELETE @work216  INSERT INTO @work(parenttable,217  childtable,218  childColumn)219  SELECT220    @TableName AS ParentTable,221    ct.TableName AS ChildTable,222    c.ColumnName AS ChildColumn223  FROM QBM_VDialogValidDynamicRef_Exp dr224  JOIN DialogColumn c225    WITH(readpast)226    ON dr.UID_DialogColumn = c.UID_DialogColumn227  JOIN DialogTable ct228    WITH(readpast)229    ON c.UID_DialogTable = ct.UID_DialogTable230  JOIN DialogTable t231    WITH(readpast)232    ON dr.UID_DialogTableReference = t.UID_DialogTable233  WHERE234    t.TableName = @TableName AND dr.parentExecuteBy = 'T' AND dr.ParentRestriction = 'DR' AND(@Operation = 'delete' AND @ParentHasXOrigin =235  0 OR @Operation = 'update' AND @ParentHasXOrigin = 1)236  WHILE 0 <(237  SELECT COUNT(*)238  FROM @work)239  BEGIN240    SELECT241      TOP 1 @AccessIndex = w.SortOrder,242      @ParentTable = w.parenttable,243      @Childtable = w.childtable,244      @ChildColumn = w.childColumn,245      @ChildColumnName = CASE246      WHEN isnull(w.childColumn,247      '') = '' THEN248      ''249    ELSE CONCAT('(',250    LEFT(w.childColumn, 64),251    ')')252    END253    FROM @work w254    ORDER BY w.SortOrder DESC255    SELECT256      @erg = @erg + '257-- Dynamic FK for Parent ' + @ParentTable + ' to Child ' + @Childtable + @ChildColumnName + '  ON PARENT DELETE RESTRICT 258	if exists (select top 1 1 259					from '260      + @childTable + ' join deleted d on d.XObjectKey = ' + @childTable + '.' + @childColumn + '261		 ' + CASE @ParentHasXOrigin262      WHEN 1 THEN263      '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'264    ELSE ''265    END + '266					)267	 begin268			RAISERROR( ''#LDS#Cannot delete {1}, because {2} does exist. Rule {0}|DynamicFK|' + @ParentTable + '|' + @childTable269    + @ChildColumnName + '|'' , 18, 2)  WITH NOWAIT270	 end271	'272    DELETE @work273    WHERE274      SortOrder = @AccessIndex275  END276  DECLARE @ColumnNames QBM_YSingleGUID277  INSERT INTO @ColumnNames(UID_SingleGuid)278  SELECT c.ColumnName AS ChildColumn279  FROM QBM_VDialogValidDynamicRef_Exp dr280  JOIN DialogColumn c281    WITH(readpast)282    ON dr.UID_DialogColumn = c.UID_DialogColumn283  JOIN DialogTable tc284    WITH(readpast)285    ON c.UID_DialogTable = tc.UID_DialogTable286  JOIN DialogTable tp287    WITH(readpast)288    ON dr.UID_DialogTableReference = tp.UID_DialogTable289  WHERE290    tc.TableName = @TableName AND dr.childExecuteBy IN('T',291  'D') AND @Operation IN('Insert',292  'update')293  GROUP BY c.ColumnName294  HAVING MAX(dr.childExecuteBy) = 'T'295  SELECT @ChildColumn = '#'296  WHILE @ChildColumn > ' '297  BEGIN298    SELECT @ChildColumn = NULL299    SELECT TOP 1 @ChildColumn = cn.UID_SingleGuid300    FROM @ColumnNames cn301    IF @ChildColumn IS NULL302    BEGIN303      CONTINUE304    END305    SELECT @ergPart = ''306    DELETE @work307    INSERT INTO @work(parenttable,308    childtable,309    childColumn)310    SELECT311      tp.TableName AS ParentTable,312      @TableName AS ChildTable,313      @ChildColumn314    FROM QBM_VDialogValidDynamicRef_Exp dr315    JOIN DialogColumn c316      WITH(readpast)317      ON dr.UID_DialogColumn = c.UID_DialogColumn318    JOIN DialogTable tc319      WITH(readpast)320      ON c.UID_DialogTable = tc.UID_DialogTable321    JOIN DialogTable tp322      WITH(readpast)323      ON dr.UID_DialogTableReference = tp.UID_DialogTable324    WHERE325      tc.TableName = @TableName AND dr.childExecuteBy IN('T',326    'D') AND @Operation IN('Insert',327    'update') AND c.ColumnName = @ChildColumn328    SELECT @ParentList = ''329    WHILE 0 <(330    SELECT COUNT(*)331    FROM @work)332    BEGIN333      SELECT334        TOP 1 @AccessIndex = w.SortOrder,335        @ParentTable = w.parenttable,336        @Childtable = w.childtable,337        @ChildColumnName = CASE338        WHEN isnull(w.childColumn,339        '') = '' THEN340        ''341      ELSE CONCAT('(',342      LEFT(w.childColumn, 64),343      ')')344      END345      FROM @work w346      ORDER BY w.SortOrder DESC347      SELECT348        @ergPart = @ergPart + '349					and not exists (select top 1 1 350									from ' + @ParentTable + ' x351										where x.XObjectKey = i.' +352        @childColumn + '353									)354		'355      IF @ParentList > ' '356      BEGIN357        SELECT @ParentList = @ParentList + ' or '358      END359      SELECT @ParentList = @ParentList + @ParentTable360      DELETE @work361      WHERE362        SortOrder = @AccessIndex363    END364    IF @ergPart > ' '365    BEGIN366      SELECT367        @erg =concat(@erg,368        '369	-- Dynamic FK for Child ',370        @childTable,371        @ChildColumnName,372        ' ON INSERT RESTRICT 373	 --#if update(',374        @childColumn,375        ')376	  --#begin377		select @InvalidValue = null378		select top 1 @InvalidValue = i.',379        @childColumn,380        '381					from inserted i382					where i.',383        @childColumn,384        ' > '' ''385		',386        @ergPart,387        '388389		if @InvalidValue > '' ''390		 begin391			select @InvalidMessage = concat( ''',392        @CannotInsertString,393        '''394											, ''DynamicFK|'' 395											, ''',396        @ParentList,397        ''' , ''|'' , ''',398        @childTable,399        @ChildColumnName,400        ''' , ''|'', @InvalidValue , ''|''401											) --concat402			RAISERROR (@InvalidMessage, 18, 2) with nowait403		  end404	  --#end405		')406      IF @Operation = 'update'407      BEGIN408        SELECT409          @erg = replace(@erg,410          '--#',411          '')412      END413      ELSE414      BEGIN415        SELECT416          @erg = replace(@erg,417          '--#',418          '--')419      END420    END421    DELETE @ColumnNames422    WHERE423      UID_SingleGuid = @ChildColumn424  END425  IF @erg > ' '426  BEGIN427    SELECT428      @erg = '429--------------------------------------------------------------------------430-- Checking dynamic FKs	431--------------------------------------------------------------------------'432      + @erg + '433--------------------------------------------------------------------------434-- / Checking dynamic FKs	435--------------------------------------------------------------------------'436  END437  endLabel:438  RETURN(@erg)439END

Open raw exported source

1 create   function dbo.QBM_FSQFKCheckDynamic (@TableName varchar(30) , @Operation varchar(30) ) returns nvarchar(max) as begin declare @erg nvarchar2(max) = '' declare @ergPart nvarchar(max) = '' declare @Childtable varchar(30) declare @ChildColumn varchar(30) declare @ParentTable varchar(30) declare3 @ChildHasXOrigin bit declare @ChildHasXIsInEffect bit declare @ParentHasXOrigin bit = dbo.QBM_FGIColumnExists(@TableName, 'XOrigin') declare @ChildColumnName4 varchar(50) declare @ParentList nvarchar(max) = '' declare @work table(parenttable varchar(30) collate database_default , childtable varchar(30) collate5 database_default , childColumn varchar(30) collate database_default , SortOrder int identity , ChildHasXOrigin bit default 0 , ChildHasXIsInEffect bit6 default 0 ) declare @AccessIndex int declare @CannotInsertString nvarchar(max) if @Operation = 'Insert' begin select @CannotInsertString = '#LDS#Cannot insert object in {2} because the associated object in {1} does not exist. Rule {0}. Invalid Value is {3}.|'7 end else begin select @CannotInsertString = '#LDS#Cannot update object in {2} because the associated object in {1} does not exist. Rule {0}. Invalid Value is {3}.|'8 end   delete  @work insert into @work(parenttable, childtable, childColumn, ChildHasXOrigin, ChildHasXIsInEffect) select @TableName as ParentTable, ct.TableName9 as ChildTable , c.ColumnName as ChildColumn , sign(LEN(ISNULL(co.UID_DialogColumn, ''))) , sign(LEN(ISNULL(ce.UID_DialogColumn, ''))) from QBM_VDialogValidDynamicRef_Exp10 dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn join DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable11 join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable left outer join DialogColumn co with (readpast) on co.UID_DialogTable12 = ct.UID_DialogTable and co.ColumnName = 'XOrigin' left outer join DialogColumn ce with (readpast) on ce.UID_DialogTable = ct.UID_DialogTable and ce.ColumnName13 = 'XIsInEffect' where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DC' and ( @Operation = 'delete' and @ParentHasXOrigin14 = 0 or @Operation = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable15 = w.parenttable , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildHasXOrigin = w.ChildHasXOrigin , @ChildHasXIsInEffect = w.ChildHasXIsInEffect16 , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left(w.childColumn, 64), ')') end from @work w order by w.SortOrder17 desc select @erg = @erg + '18-- Dynamic FK  for Parent ' + @ParentTable + ' to Child ' + @childTable + @ChildColumnName + ' ON PARENT DELETE CASCADE 19	 if exists( select top 1 1 20				from deleted d join '21 + @childTable + ' x on d.XObjectKey = x.' + @childColumn + '22		 ' + case @ParentHasXOrigin when 1 then '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'23 else '' end + '24			  )25	  begin	' if @ChildHasXOrigin = 1 begin select @erg = @erg + '	  --  (modify XOrigin)26			update ' + @childTable + '27			 set XOrigin = 0'28 if @ChildHasXIsInEffect = 1 begin select @erg = @erg + ', XIsInEffect = 0' end if dbo.QBM_FGIColumnExistsInSchema(@childTable, 'XDateUpdated') = 1 begin29 select @erg = @erg + ', XDateUpdated = @Xdate, XUserUpdated = @XUser' end end else begin select @erg = @erg + '	  30			delete ' + @childTable end select31 @erg = @erg + '32		 from ' + @childTable + ' join deleted d on d.XObjectKey = ' + @childTable + '.' + @childColumn + '33		 ' + case @ParentHasXOrigin 34when 1 then '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0' else '' end + '35	  end		36	' delete37  @work  where SortOrder = @AccessIndex end  delete  @work insert into @work(parenttable, childtable, childColumn) select @TableName as ParentTable, ct.TableName38 as ChildTable , c.ColumnName as ChildColumn from QBM_VDialogValidDynamicRef_Exp dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn39 join DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable40 where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DS' and ( @Operation = 'delete' and @ParentHasXOrigin = 0 or @Operation41 = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable = w.parenttable42 , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left43(w.childColumn, 64), ')') end from @work w order by w.SortOrder desc select @erg = @erg + '44-- Dynamic FK  for Parent ' + @ParentTable + ' to Child ' 45+ @childTable + @ChildColumnName + ' ON PARENT DELETE SET NULL 46	 if exists( select top 1 1 47				from deleted d join ' + @childTable + ' x on d.XObjectKey = x.'48 + @childColumn + '49		 ' + case @ParentHasXOrigin when 1 then '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'50 else '' end + '51			  )52	  begin		  53		update ' + @childTable + '54			set ' + @childColumn + ' = null ' + case when dbo.QBM_FGIColumnExistsInSchema(@Childtable55, 'XDateUpdated') = 1 and @ChildColumn not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = @Xdate, XUserUpdated = @XUser' else '' end + '56		 from '57 + @childTable + ' join deleted d on d.XObjectKey = ' + @childTable + '.' + @childColumn + '58		 ' + case @ParentHasXOrigin when 1 then '								join '59 + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0' else '' end + '60	  end		61	' delete  @work where SortOrder =62 @AccessIndex end   delete  @work insert into @work(parenttable, childtable, childColumn) select @TableName as ParentTable, ct.TableName as ChildTable 63, c.ColumnName as ChildColumn from QBM_VDialogValidDynamicRef_Exp dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn join64 DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable65 where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DR' and ( @Operation = 'delete' and @ParentHasXOrigin = 0 or @Operation66 = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable = w.parenttable67 , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left68(w.childColumn, 64), ')') end from @work w order by w.SortOrder desc select @erg = @erg + '69-- Dynamic FK for Parent ' + @ParentTable + ' to Child ' +70 @Childtable + @ChildColumnName + '  ON PARENT DELETE RESTRICT 71	if exists (select top 1 1 72					from ' + @childTable + ' join deleted d on d.XObjectKey = '73 + @childTable + '.' + @childColumn + '74		 ' + case @ParentHasXOrigin when 1 then '								join ' + @ParentTable + ' p on d.XObjectKey = p.XObjectKey and d.XOrigin > 0 and p.XOrigin = 0'75 else '' end + '76					)77	 begin78			RAISERROR( ''#LDS#Cannot delete {1}, because {2} does exist. Rule {0}|DynamicFK|' + @ParentTable + '|' + @childTable79 + @ChildColumnName + '|'' , 18, 2)  WITH NOWAIT80	 end81	' delete  @work  where SortOrder = @AccessIndex end    declare @ColumnNames QBM_YSingleGUID  82insert into @ColumnNames(UID_SingleGuid ) select c.ColumnName as ChildColumn from QBM_VDialogValidDynamicRef_Exp dr join DialogColumn c with (readpast)83 on dr.UID_DialogColumn = c.UID_DialogColumn join DialogTable tc with (readpast) on c.UID_DialogTable = tc.UID_DialogTable join DialogTable tp with (readpast84) on dr.UID_DialogTableReference = tp.UID_DialogTable where tc.TableName = @TableName and dr.childExecuteBy in ( 'T', 'D')   and @Operation in ('Insert'85, 'update')  group by c.ColumnName having MAX(dr.childExecuteBy) = 'T' select @ChildColumn = '#' while @ChildColumn > ' ' begin select @ChildColumn = null86 select top 1 @ChildColumn = cn.UID_SingleGuid  from @ColumnNames cn if @ChildColumn is null begin continue end select @ergPart = '' delete  @work  insert87 into @work(parenttable, childtable, childColumn) select tp.TableName as ParentTable, @TableName as ChildTable, @ChildColumn from QBM_VDialogValidDynamicRef_Exp88 dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn join DialogTable tc with (readpast) on c.UID_DialogTable = tc.UID_DialogTable89 join DialogTable tp with (readpast) on dr.UID_DialogTableReference = tp.UID_DialogTable where tc.TableName = @TableName and dr.childExecuteBy in ( 'T'90, 'D')   and @Operation in ('Insert', 'update') and c.ColumnName = @ChildColumn select @ParentList = '' while 0 < (select COUNT(*) from @work) begin select91 top 1 @AccessIndex = w.SortOrder , @ParentTable = w.parenttable , @Childtable = w.childtable , @ChildColumnName = case when isnull(w.childColumn, '') 92= '' then '' else concat('(', left(w.childColumn, 64), ')') end from @work w order by w.SortOrder desc select @ergPart = @ergPart + '93					and not exists (select top 1 1 94									from '95 + @ParentTable + ' x96										where x.XObjectKey = i.' + @childColumn + '97									)98		' if @ParentList > ' ' begin select @ParentList = @ParentList99 + ' or ' end select @ParentList = @ParentList + @ParentTable delete  @work  where SortOrder = @AccessIndex end if @ergPart > ' ' begin select @erg =concat100( @erg , '101	-- Dynamic FK for Child ' , @childTable , @ChildColumnName, ' ON INSERT RESTRICT 102	 --#if update(' , @childColumn , ')103	  --#begin104		select @InvalidValue = null105		select top 1 @InvalidValue = i.'106 , @childColumn , '107					from inserted i108					where i.' , @childColumn , ' > '' ''109		' , @ergPart , '110111		if @InvalidValue > '' ''112		 begin113			select @InvalidMessage = concat( '''114, @CannotInsertString, '''115											, ''DynamicFK|'' 116											, ''', @ParentList, ''' , ''|'' , ''', @childTable, @ChildColumnName , ''' , ''|'', @InvalidValue , ''|''117											) --concat118			RAISERROR (@InvalidMessage, 18, 2) with nowait119		  end120	  --#end121		'122 )  if @Operation = 'update' begin select @erg = replace(@erg, '--#', '') end else begin select @erg = replace(@erg, '--#', '--') end end delete  @ColumnNames123  where UID_SingleGuid  = @ChildColumn end  if @erg > ' ' begin select @erg = '124--------------------------------------------------------------------------125-- Checking dynamic FKs	126--------------------------------------------------------------------------'127 + @erg + '128--------------------------------------------------------------------------129-- / Checking dynamic FKs	130--------------------------------------------------------------------------'131 end endLabel: return(@erg) end 132