Back to OIM Explorer

dbo.DPR_FSQTriggerRIMemberShip

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.902 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_FCVStringToIndent source text reference
references source dbo.QBM_FGITableName source text reference

References

Referenced By

dbo.DPR_PTriggerCreateMembership

Complete Source

1CREATE FUNCTION dbo.DPR_FSQTriggerRIMemberShip(2  @ParentTable varchar(30),3  @Operation varchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @Erg nvarchar(max) = ''9  DECLARE @Pattern nvarchar(max) = '1011if exists (select top 1 112			from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%13			)14 begin15	delete DPRMemberShipAction16	from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%17 end18 '19  IF @Operation <> 'Delete'20  BEGIN21    GOTO endlabel22  END23  SELECT24    @erg = string_agg(convert(nvarchar(max), y.element),25    N '')26  FROM(27  SELECT28    DISTINCT replace(@pattern, '%key%', CASE29    WHEN x.ContainerTable = @ParentTable THEN30    'ObjectKeyBase'31    WHEN x.MemberTable = @ParentTable THEN32    'ObjectKeyMember'33  ELSE 'ObjectKeyMN'34  END) AS element35  FROM(36  SELECT37    r.ParentTable AS ContainerTable, r.ParentColumn AS ContainerColumn, r.ChildColumn AS ChildConnectContainer,38    rm.ParentTable AS MemberTable, rm.ParentColumn AS MemberColumn, rm.ChildColumn AS ChildConnectMember,39    nht.UID_DPRNameSpace, tc.TableName40  FROM QBM_VQBMRelation r41  JOIN DPRNameSpaceHasDialogTable nht42    WITH(readpast)43    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 144  JOIN QBM_VQBMRelation rm45    ON r.UID_QBMRelationMN = rm.UID_QBMRelation46  JOIN DialogTable tc47    WITH(readpast)48    ON r.ChildTable = tc.TableName AND tc.isMNTable = 149  WHERE50    (r.ParentTable = @ParentTable OR rm.ParentTable = @ParentTable) AND r.IsForUpdateXDateSubItem = 1 AND nht.IsAdHocSingleMemberShip = 151  UNION52  SELECT53    r.ParentTable AS ContainerTable, r.ParentColumn AS ContainerColumn, r.ChildColumn AS ChildConnectContainer,54    dbo.QBM_FGITableName(rm.UID_DialogTableReference) AS MemberTable, 'XObjectKey' AS MemberColumn, ok.ColumnName AS ChildConnectMember,55    nht.UID_DPRNameSpace, tc.TableName56  FROM QBM_VQBMRelation r57  JOIN DPRNameSpaceHasDialogTable nht58    WITH(readpast)59    ON r.UID_DialogTableChild = nht.UID_DialogTable AND r.IsForUpdateXDateSubItem = 160  JOIN DialogColumn ok61    WITH(readpast)62    ON ok.UID_DialogTable = r.UID_DialogTableChild63  JOIN DialogValidDynamicRef rm64    WITH(readpast)65    ON ok.UID_DialogColumn = rm.UID_DialogColumn66  JOIN DialogTable tc67    WITH(readpast)68    ON ok.UID_DialogTable = tc.UID_DialogTable AND tc.IsMAllTable = 169  WHERE70    (r.ParentTable = @ParentTable OR dbo.QBM_FGITableName(rm.UID_DialogTableReference) = @ParentTable) AND r.IsForUpdateXDateSubItem = 1 AND71  nht.IsAdHocSingleMemberShip = 1) AS x) AS y72  IF @Erg > ' '73  BEGIN74    SELECT75      @Erg = '76----------------------------------------------------------77-- Handle RI for DPRMemberShipAction78----------------------------------------------------------79 '80      + dbo.QBM_FCVStringToIndent(@Erg,81      1) + '82  83----------------------------------------------------------84-- / Handle RI for DPRMemberShipAction85----------------------------------------------------------'86  END87  ELSE88  BEGIN89    SELECT @erg = ''90  END91  endLabel:92  RETURN(@erg)93END

Open raw exported source

1  create   function dbo.DPR_FSQTriggerRIMemberShip(@ParentTable varchar(30) , @Operation varchar(16) ) returns nvarchar(max) as begin declare @Erg2 nvarchar(max) = '' declare @Pattern nvarchar(max) = '34if exists (select top 1 15			from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%6			)7 begin8	delete DPRMemberShipAction9	from deleted d join DPRMemberShipAction m on d.XObjectkey = m.%key%10 end11 '12 if @Operation <> 'Delete' begin goto endlabel end  select @erg = string_agg(convert(nvarchar(max), y.element )  , N'' )  from ( select distinct replace13 (@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.ParentTable15 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_VQBMRelation17 rm on r.UID_QBMRelationMN = rm.UID_QBMRelation join DialogTable tc with (readpast) on r.ChildTable = tc.TableName and tc.isMNTable = 1 where (r.ParentTable18 = @ParentTable  or rm.ParentTable = @ParentTable ) and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 union select r.ParentTable as19 ContainerTable , r.ParentColumn as ContainerColumn , r.ChildColumn as ChildConnectContainer , dbo.QBM_FGITableName(rm.UID_DialogTableReference) as MemberTable20 , 'XObjectKey' as MemberColumn , ok.ColumnName as ChildConnectMember , nht.UID_DPRNameSpace , tc.TableName from QBM_VQBMRelation r join DPRNameSpaceHasDialogTable21 nht with (readpast) on r.UID_DialogTableChild = nht.UID_DialogTable and r.IsForUpdateXDateSubItem = 1 join DialogColumn ok with (readpast) on ok.UID_DialogTable22 = r.UID_DialogTableChild join DialogValidDynamicRef rm with (readpast) on ok.UID_DialogColumn = rm.UID_DialogColumn join DialogTable tc with (readpast23) on ok.UID_DialogTable = tc.UID_DialogTable and tc.IsMAllTable = 1 where (r.ParentTable = @ParentTable or dbo.QBM_FGITableName(rm.UID_DialogTableReference24) = @ParentTable ) and r.IsForUpdateXDateSubItem = 1 and nht.IsAdHocSingleMemberShip = 1 ) as x ) as y if @Erg > ' ' begin select @Erg = '25----------------------------------------------------------26-- Handle RI for DPRMemberShipAction27----------------------------------------------------------28 '29 + dbo.QBM_FCVStringToIndent(@Erg, 1) + '30  31----------------------------------------------------------32-- / Handle RI for DPRMemberShipAction33----------------------------------------------------------'34 end else begin select @erg = '' end endLabel: return(@erg) end 35