dbo.QBM_FSQFKCheckFix

1  create   function dbo.QBM_FSQFKCheckFix (@UID_QBMRelation varchar(38), @FragmentType nvarchar(3) , @Operation varchar(16)  ) returns nvarchar(max
2) as begin declare @muster nvarchar(max) declare @erg nvarchar(max) = N'' declare @ParentColumn nvarchar(64) declare @ChildColumn nvarchar(64) declare 
3@ChildTable nvarchar(64) declare @ConstraintName nvarchar(64) declare @ParentTable nvarchar(64) declare @ChildColumnName nvarchar(64) declare @CannotInsertString
4 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}.|'
5 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}.|'
6 end select top 1 @ChildTable = r.ChildTable , @ParentTable = r.ParentTable , @ParentColumn = r.ParentColumn , @ChildColumn = r.ChildColumn , @ChildColumnName
7 = case when isnull(c.ColumnName, '') = '' then '' else concat('(', left(c.ColumnName, 64), ')') end from QBM_VQBMRelation r left outer join DialogColumn
8 c on r.UID_ChildColumn = c.UID_DialogColumn where r.UID_QBMRelation = @UID_QBMRelation select @ConstraintName = dbo.QBM_FSQConstraintName (@UID_QBMRelation
9)  declare @ColumnUpdatedThis nvarchar(max) = N''  declare @joinInsertedChild nvarchar(max) = N''  declare @joinInsertedParent nvarchar(max)= N''  declare
10 @joinDeletedChild nvarchar(max)= N''  declare @SetInsertedFKNull nvarchar(max)= N''  declare @setChildFKNull nvarchar(max)= N''  declare @InsertedIsNotNull
11 nvarchar(max)= N'' declare @StringPatternPwo varchar(16) declare @StringPatternPwoInv varchar(16) select @StringPatternPwo = dbo.QBM_FCVBinaryToString
12( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Pwo|',0)), 0) select @StringPatternPwoInv = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin
13('|Pwo|',1)), 0) select @ColumnUpdatedThis = N'update(' + @ChildColumn + ')' select @joinInsertedChild = N'inserted.' + @childColumn + N' = ' + @ChildTable
14 + N'.' + @ChildColumn select @joinInsertedParent = N'inserted.' + @ChildColumn + N' = ' + @ParentTable + N'.' + @ParentColumn select @joinDeletedChild
15 = N'deleted.' + @parentColumn + N' = ' + @ChildTable + N'.' + @ChildColumn select @SetInsertedFKNull = @ChildTable + N'.' + @ChildColumn + N' = null '
16 select @setChildFKNull = @ChildTable + N'.' + @ChildColumn + N' = null ' select @InsertedIsNotNull = N'inserted.' + @ChildColumn + N' > '' '' '  select
17 @muster = case @Fragmenttype  when 'DS' then '
18		 if %ColumnUpdatedThis%
19			 begin
20				insert into @SimulationModeBuffer (operation, BaseObjectType, ColumnName, Objectkey, OldValue) 
21								select ''U'', ''%ChildTable%'', ''%ChildColumn%'', deleted.XObjectKey , isnull(convert( nvarchar(255), deleted.%ChildColumn% ), N'''')
22								from deleted
23								where deleted.%ChildColumn% is not null
24									and exists (select top 1 1 from inserted 
25													where %ChildColumn% is null
26												)
27			 end
28'
29  when 'CDC' then 'alter table %ChildTable% with nocheck add constraint %ConstraintName% foreign Key (%ChildColumn%) references %ParentTable% (%ParentColumn%) on delete cascade not for Replication'
30 when 'CDR'  then 'alter table %ChildTable% with nocheck add constraint %ConstraintName% foreign Key (%ChildColumn%) references %ParentTable% (%ParentColumn%) on delete no action not for Replication'
31 when 'CDS' then 'alter table %ChildTable% with nocheck add constraint %ConstraintName% foreign Key (%ChildColumn%) references %ParentTable% (%ParentColumn%) on delete set null not for Replication'
32  when 'TIR' then concat('
33	-- Relation %ConstraintName% Parent %ParentTable% to Child %Childtable%%ChildColumnName% ON CHILD INSERT RESTRICT 
34	 if %ColumnUpdatedThis%
35	  begin
36		select @InvalidValue = null
37		select top 1 @InvalidValue = inserted.%ChildColumn%
38					from inserted left outer join %ParentTable% t on inserted.'
39,'%ChildColumn%',' = t.', '%ParentColumn%' , '
40					where %InsertedIsNotNull%
41					and t.', '%ParentColumn%' , ' is null
42
43		if @InvalidValue > '' ''
44		 begin
45			select @InvalidMessage = concat( '''
46, @CannotInsertString, '''
47											, ''%ConstraintName%|'' 
48											, ''%ParentTable%'' , ''|'' , ''%childTable%%ChildColumnName%'' , ''|'', @InvalidValue , ''|''
49											) --concat
50			RAISERROR (@InvalidMessage, 18, 2) with nowait
51		  end
52	  end
53'
54 )  when 'TII'  then concat('
55	-- Relation %ConstraintName% Parent %ParentTable% to Child %Childtable%%ChildColumnName% ON CHILD INSERT RESTRICT 
56	select @InvalidValue = null
57	select top 1 @InvalidValue = inserted.%ChildColumn%
58				from inserted left outer join %ParentTable% t on inserted.'
59,'%ChildColumn%',' = t.', '%ParentColumn%' , '
60				where %InsertedIsNotNull%
61				and t.', '%ParentColumn%' , ' is null
62
63	if @InvalidValue > '' ''
64	 begin
65		select @InvalidMessage = concat( '''
66, @CannotInsertString, '''
67										, ''%ConstraintName%|'' 
68										, ''%ParentTable%'' , ''|'' , ''%childTable%%ChildColumnName%'' , ''|'', @InvalidValue , ''|''
69										) --concat
70		RAISERROR (@InvalidMessage, 18, 2) with nowait
71	 end
72'
73 )  when 'TIS' then '
74-- Relation %ConstraintName% Parent %ParentTable% to Child %Childtable% ON CHILD INSERT SET NULL 
75 if %ColumnUpdatedThis%
76  begin
77	if exists (select top 1 1 
78				from %ChildTable%,inserted
79				where
80					%joinInsertedChild%