dbo.DPR_FSQTriggerRIMemberShip

1  create   function dbo.DPR_FSQTriggerRIMemberShip(@ParentTable varchar(30) , @Operation varchar(16) ) returns nvarchar(max) as begin declare @Erg
2 nvarchar(max) = '' declare @Pattern nvarchar(max) = '
3
4if exists (select top 1 1
5			from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%
6			)
7 begin
8	delete DPRMemberShipAction
9	from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%
10 end
11 '
12 if @Operation <> 'Delete' begin goto endlabel end  select @erg = string_agg(convert(nvarchar(max), y.element )  , N'' )  from ( select distinct replace
13 (@pattern, '%key%' , case when x.ContainerTable = @ParentTable then 'ObjectKeyBase' when x.MemberTable = @ParentTable then 'ObjectKeyMember' else 'ObjectKeyMN'
14  end ) as element  from (  select r.ParentTable as ContainerTable , r.ParentColumn as ContainerColumn , r.ChildColumn as ChildConnectContainer , rm.ParentTable
15 as MemberTable , rm.ParentColumn as MemberColumn , rm.ChildColumn as ChildConnectMember , nht.UID_DPRNameSpace , tc.TableName from QBM_VQBMRelation r 
16join DPRNameSpaceHasDialogTable nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem = 1 join QBM_VQBMRelation
17 rm on r.UID_QBMRelationMN = rm.UID_QBMRelation join DialogTable tc with (readpast) on r.ChildTable = tc.TableName and tc.isMNTable = 1 where (r.ParentTable
18 = @ParentTable  or rm.ParentTable = @ParentTable ) and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 union select r.ParentTable as
19 ContainerTable , r.ParentColumn as ContainerColumn , r.ChildColumn as ChildConnectContainer , dbo.QBM_FGITableName(rm.UID_DialogTableReference) as MemberTable
20 , 'XObjectKey' as MemberColumn , ok.ColumnName as ChildConnectMember , nht.UID_DPRNameSpace , tc.TableName from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable
21 nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem = 1 join DialogColumn ok with (readpast) on ok.UID_DialogTable
22 = r.UID_DialogTableChild join DialogValidDynamicRef rm with (readpast) on ok.UID_DialogColumn = rm.UID_DialogColumn join DialogTable tc with (readpast
23) on ok.UID_DialogTable = tc.UID_DialogTable and tc.IsMAllTable = 1 where (r.ParentTable = @ParentTable or dbo.QBM_FGITableName(rm.UID_DialogTableReference
24) = @ParentTable ) and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 ) as x ) as y if @Erg > ' ' begin select @Erg = '
25----------------------------------------------------------
26-- Handle RI for DPRMemberShipAction
27----------------------------------------------------------
28 '
29 + dbo.QBM_FCVStringToIndent(@Erg, 1) + '
30  
31----------------------------------------------------------
32-- / Handle RI for DPRMemberShipAction
33----------------------------------------------------------'
34 end else begin select @erg = '' end endLabel: return(@erg) end 
35