TR_TEAMFUNDRAISINGTEAM_UPDATE_FAFGROUPDIRECTHIERARCHY

        create trigger TR_TEAMFUNDRAISINGTEAM_UPDATE_FAFGROUPDIRECTHIERARCHY on dbo.TEAMFUNDRAISINGTEAM after update not for replication
        as
            if update(PARENTTEAMID) 
            begin
                declare @CHANGEAGENTID uniqueidentifier, 
                        @CURRENTDATE datetime

                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
                set @CURRENTDATE = GETDATE()

                ;With CTE_PARENT(TEAMID, PARENTTEAMID)
                 as
                (
                    select D.ID, D.PARENTTEAMID
                    from deleted D 
              join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = D.ID
                    union all
                    select C.TEAMID, TFT.PARENTTEAMID
                    from dbo.TEAMFUNDRAISINGTEAM TFT
                    join CTE_PARENT C ON TFT.ID = C.PARENTTEAMID 
                )
                ,CTE_CHILDREN(TEAMID, PARENTTEAMID)
                as
                (
                    select D.ID, D.PARENTTEAMID
                    from deleted D
              join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = D.ID
                    union all    
                    select TFT.ID, C.PARENTTEAMID
                    from dbo.TEAMFUNDRAISINGTEAM TFT
                    join CTE_CHILDREN C ON TFT.PARENTTEAMID = C.TEAMID
                ),
                CTE_ALL(TEAMID, PARENTTEAMID)
                AS
                (
                    select TEAMID, PARENTTEAMID from CTE_PARENT 
                    union all 
                    select TEAMID, PARENTTEAMID from CTE_CHILDREN
                )
                delete from dbo.FAFGROUPDIRECTHIERARCHY  
                from dbo.FAFGROUPDIRECTHIERARCHY FF
                    join CTE_ALL C on FF.GROUPID = C.PARENTTEAMID and FF.CHILDGROUPID = C.TEAMID

                ;With CTE_PARENT(TEAMID, PARENTTEAMID)
                 as
                (
                    select I.ID, I.PARENTTEAMID
                    from inserted  I
              join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = I.ID
                    union all
                    select C.TEAMID, TFT.PARENTTEAMID
                    from dbo.TEAMFUNDRAISINGTEAM TFT
                    join CTE_PARENT C ON TFT.ID = C.PARENTTEAMID 
                )
                ,CTE_CHILDREN(TEAMID, PARENTTEAMID)
                as
                (                    
                    select I.ID, I.PARENTTEAMID
                    from inserted  I
              join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = I.ID
                    union all    
                    select TFT.ID, C.PARENTTEAMID
                    from dbo.TEAMFUNDRAISINGTEAM TFT
                    join CTE_CHILDREN C ON TFT.PARENTTEAMID = C.TEAMID
                ),
                CTE_ALL(TEAMID, PARENTTEAMID)
                AS
                (
                    select TEAMID, PARENTTEAMID from CTE_PARENT 
                    union all 
                    select TEAMID, PARENTTEAMID from CTE_CHILDREN
                )

                insert into dbo.FAFGROUPDIRECTHIERARCHY(ID, EVENTID, GROUPID, GROUPCONSTITUENTID, CHILDGROUPID, CHILDGROUPCONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select newID(), A.*,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from (
                    select distinct cTX.EVENTID, C.PARENTTEAMID as GROUPID , pTX.TEAMCONSTITUENTID as GROUPCONSTITUENTID, C.TEAMID as CHILDGROUPID, cTX.TEAMCONSTITUENTID as CHILDGROUPCONSTITUENTID
                    from CTE_ALL c
                    left join dbo.TEAMEXTENSION cTX (nolock) on cTX.TEAMFUNDRAISINGTEAMID = C.TEAMID
                    left join dbo.TEAMEXTENSION pTX (nolock) on pTX.TEAMFUNDRAISINGTEAMID = C.PARENTTEAMID
                    where C.PARENTTEAMID is not null 
                ) A
            end