dbo.QBM_PQBMRelationCheck

1  create   procedure QBM_PQBMRelationCheck as begin declare @CountItems int = 0 declare @DebugSwitch int = 0 declare @XUser nvarchar(64) = object_name
2(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY update QBMRelation set ParentExecuteBy = left(v.ValidParentExecuteBy, 1)
3 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation r join DialogColumn cc on r.uid_ChildColumn = cc.UID_DialogColumn join DialogTable c 
4on cc.UID_DialogTable = c.UID_DialogTable join DialogColumn cp on r.UID_ParentColumn = cp.UID_DialogColumn join DialogTable p on cp.UID_DialogTable = p.UID_DialogTable
5 join dbo.QBM_FTRelationValidationInfo() v on v.RIType = 'Fix' and v.Parenttype = p.TableType and v.ChildType = c.TableType where v.ValidParentExecuteBy
6 not like '%' + r.ParentExecuteBy + '%' and v.IsValid = 1 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'parentexecuteby geändert' + 
7str(@CountItems) end update QBMRelation set ChildExecuteBy = left(v.ValidChildExecuteBy, 1) , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation
8 r join DialogColumn cc on r.UID_ChildColumn = cc.UID_DialogColumn join DialogTable c on cc.UID_DialogTable = c.UID_DialogTable join DialogColumn cp on
9 r.UID_ParentColumn = cp.UID_DialogColumn join DialogTable p on cp.UID_DialogTable = p.UID_DialogTable join dbo.QBM_FTRelationValidationInfo() v on v.RIType
10 = 'Fix' and v.Parenttype = p.TableType and v.ChildType = c.TableType where v.ValidChildExecuteBy not like '%' + r.ChildExecuteBy + '%' and v.IsValid =
11 1 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Childexecuteby' + str(@CountItems) end  update DialogColumn set isForeignKey = sign
12(LEN(isnull(r.UID_ChildColumn, ''))) , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c left outer join QBMRelation r on r.UID_ChildColumn
13 = c.UID_DialogColumn where isnull(c.isForeignKey,0) <> sign(LEN(isnull(r.UID_ChildColumn, '')))    update QBMRelation set IsMNRelation = 0  , UID_QBMRelationMN
14 = null , XDateUpdated = @Xdate , XUserUpdated = @XUser where IsMNRelation = 1  or UID_QBMRelationMN > ' ' update QBMRelation set isMNRelation = 1   , 
15UID_QBMRelationMN = x.MNRelationUID , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation , ( select linkscr.UID_QBMRelation ,linkscr.UID_DialogTableParent
16, linkscr.UID_DialogTableChild as mnTable, rechtscr.UID_DialogTableParent as mnPartnerTable , rechtscr.UID_QBMRelation as MNRelationUID , rechtscr.RelationID
17 as MNRelationID from QBM_VQBMRelation linksCR join QBM_VQBMRelation rechtsCR on linksCR.UID_DialogTableChild = rechtsCR.UID_DialogTableChild and linksCR.UID_QBMRelation
18 <> rechtsCR.UID_QBMRelation join ( select UID_DialogTable , count(*) as CountItems from dialogcolumn where ispkmember = 1 and UID_DialogTable in (select
19 r.UID_DialogTableChild from QBM_VQBMRelation r ) group by UID_DialogTable having count(*) > 1 ) as n1 on n1.UID_DialogTable = linkscr.UID_DialogTableChild
20 join ( select UID_DialogTableChild, count(*) as CountItems from QBM_VQBMRelation  group by UID_DialogTableChild having count(*) > 1 ) as n2 on n2.UID_DialogTableChild
21 = linkscr.UID_DialogTableChild join ( select dcr.UID_QBMRelation from QBMRelation dcr join dialogcolumn dc on dcr.uid_childcolumn = dc.uid_dialogcolumn
22 and dc.isPKMember = 1 ) as n3 on n3.UID_QBMRelation = linkscr.UID_QBMRelation join ( select dcr.UID_QBMRelation from QBMRelation dcr join dialogcolumn
23 dc on dcr.uid_childcolumn = dc.uid_dialogcolumn and dc.isPKMember = 1 ) as n4 on n4.UID_QBMRelation = rechtscr.UID_QBMRelation ) as x where QBMRelation.UID_QBMRelation
24 = x.UID_QBMRelation  and QBMRelation.ParentExecuteBy in ('T', 'D' , 'N'  )  and QBMRelation.IsMNRelation = 0  update DialogTable set isMNTable = 0  , 
25XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t where t.TableType not in (  'U', 'R') and t.isMNTable = 1 and Not exists (select top 1
26 1 from QBMRelation r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = t.UID_DialogTable and r.ismnrelation = 1 
27) update dialogtable set ismntable = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t where exists (select top 1 1 from QBMRelation
28 r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = t.UID_DialogTable and r.ismnrelation = 1 )  and t.isMNTable 
29= 0 and t.TableType not in ( 'U', 'R')  update DialogTable set IsMAllTable = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t join
30 QBM_VMAllTables v on t.UID_DialogTable = v.UID_DialogTable where t.IsMAllTable = 0 and t.TableType not in ( 'U', 'R') update DialogTable set IsMAllTable
31 = 0 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t left outer join QBM_VMAllTables v on t.UID_DialogTable = v.UID_DialogTable where
32 t.IsMAllTable = 1 and v.UID_DialogTable is null and t.TableType not in ( 'U', 'R')  update DialogColumn set IsMAllKeyMember = 1 , XDateUpdated = @Xdate
33 , XUserUpdated = @XUser  from DialogColumn c join QBM_VMAllTables v on c.UID_DialogTable = v.UID_DialogTable and ( c.UID_DialogColumn = v.UID_DialogColumnFK
34 or c.UID_DialogColumn = v.UID_DialogColumnObjectKey ) where c.IsMAllKeyMember = 0 update DialogColumn set IsMAllKeyMember = 0 , XDateUpdated = @Xdate 
35, XUserUpdated = @XUser  from DialogColumn c left outer join QBM_VMAllTables v on c.UID_DialogTable = v.UID_DialogTable and ( c.UID_DialogColumn = v.UID_DialogColumnFK
36 or c.UID_DialogColumn = v.UID_DialogColumnObjectKey ) where c.IsMAllKeyMember = 1 and v.UID_DialogTable is null    select @CountItems = 1 while @CountItems
37 > 0 begin select @CountItems = 0               update QBMRelation set parentAllowUpdate = b.parentAllowUpdate, parentrestriction = b.parentrestriction
38 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and (QBMRelation.parentAllowUpdate
39 <> b.parentAllowUpdate or QBMRelation.parentrestriction <> b.parentrestriction ) and b.parentAllowUpdate = 0 select @CountItems = @CountItems + @@rowcount
40 update QBMRelation set ChildAllowUpdate = b.ChildAllowUpdate, ChildRestriction = b.ChildRestriction , XDateUpdated = @Xdate , XUserUpdated = @XUser from
41 QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and ( QBMRelation.ChildAllowUpdate <> b.ChildAllowUpdate or QBMRelation.ChildRestriction
42 <> b.ChildRestriction ) and b.ChildAllowUpdate = 0 select @CountItems = @CountItems + @@rowcount   update QBMRelation set parentAllowUpdate = 0 , XDateUpdated
43 = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.parentAllowUpdate
44 <> 0 and b.parentAllowUpdate = 1 and b.Parentexecuteby = 'T' select @CountItems = @CountItems + @@rowcount update QBMRelation set ChildAllowUpdate = 0
45 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.ChildAllowUpdate
46 <> 0 and b.ChildAllowUpdate = 1 and b.Childexecuteby = 'T' select @CountItems = @CountItems + @@rowcount  update QBMRelation set parentAllowUpdate = 1
47 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.parentAllowUpdate
48 <> 1 and b.parentAllowUpdate = 1 and b.Parentexecuteby = 'D' select @CountItems = @CountItems + @@rowcount update QBMRelation set ChildAllowUpdate = 1
49 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.ChildAllowUpdate
50 <> 1 and b.ChildAllowUpdate = 1 and b.Childexecuteby = 'D' select @CountItems = @CountItems + @@rowcount end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
51 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 
52