dbo.QBM_FSQFKCheckDynamic

1 create   function dbo.QBM_FSQFKCheckDynamic (@TableName varchar(30) , @Operation varchar(30) ) returns nvarchar(max) as begin declare @erg nvarchar
2(max) = '' declare @ergPart nvarchar(max) = '' declare @Childtable varchar(30) declare @ChildColumn varchar(30) declare @ParentTable varchar(30) declare
3 @ChildHasXOrigin bit declare @ChildHasXIsInEffect bit declare @ParentHasXOrigin bit = dbo.QBM_FGIColumnExists(@TableName, 'XOrigin') declare @ChildColumnName
4 varchar(50) declare @ParentList nvarchar(max) = '' declare @work table(parenttable varchar(30) collate database_default , childtable varchar(30) collate
5 database_default , childColumn varchar(30) collate database_default , SortOrder int identity , ChildHasXOrigin bit default 0 , ChildHasXIsInEffect bit
6 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.TableName
9 as ChildTable , c.ColumnName as ChildColumn , sign(LEN(ISNULL(co.UID_DialogColumn, ''))) , sign(LEN(ISNULL(ce.UID_DialogColumn, ''))) from QBM_VDialogValidDynamicRef_Exp
10 dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn join DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable
11 join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable left outer join DialogColumn co with (readpast) on co.UID_DialogTable
12 = ct.UID_DialogTable and co.ColumnName = 'XOrigin' left outer join DialogColumn ce with (readpast) on ce.UID_DialogTable = ct.UID_DialogTable and ce.ColumnName
13 = 'XIsInEffect' where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DC' and ( @Operation = 'delete' and @ParentHasXOrigin
14 = 0 or @Operation = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable
15 = w.parenttable , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildHasXOrigin = w.ChildHasXOrigin , @ChildHasXIsInEffect = w.ChildHasXIsInEffect
16 , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left(w.childColumn, 64), ')') end from @work w order by w.SortOrder
17 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 begin
29 select @erg = @erg + ', XDateUpdated = @Xdate, XUserUpdated = @XUser' end end else begin select @erg = @erg + '	  
30			delete ' + @childTable end select
31 @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	' delete
37  @work  where SortOrder = @AccessIndex end  delete  @work insert into @work(parenttable, childtable, childColumn) select @TableName as ParentTable, ct.TableName
38 as ChildTable , c.ColumnName as ChildColumn from QBM_VDialogValidDynamicRef_Exp dr join DialogColumn c with (readpast) on dr.UID_DialogColumn = c.UID_DialogColumn
39 join DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable
40 where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DS' and ( @Operation = 'delete' and @ParentHasXOrigin = 0 or @Operation
41 = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable = w.parenttable
42 , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left
43(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(@Childtable
55, '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 join
64 DialogTable ct with (readpast) on c.UID_DialogTable = ct.UID_DialogTable join DialogTable t with (readpast) on dr.UID_DialogTableReference = t.UID_DialogTable
65 where t.TableName = @TableName and dr.parentExecuteBy = 'T' and dr.ParentRestriction = 'DR' and ( @Operation = 'delete' and @ParentHasXOrigin = 0 or @Operation
66 = 'update' and @ParentHasXOrigin = 1 ) while 0 < (select COUNT(*) from @work) begin select top 1 @AccessIndex = w.SortOrder , @ParentTable = w.parenttable
67 , @Childtable = w.childtable , @ChildColumn = w.childColumn , @ChildColumnName = case when isnull(w.childColumn, '') = '' then '' else concat('(', left
68(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	 begin
78			RAISERROR( ''#LDS#Cannot delete {1}, because {2} does exist. Rule {0}|DynamicFK|' + @ParentTable + '|' + @childTable
79 + @ChildColumnName + '|'' , 18, 2)  WITH NOWAIT
80	 end