TR_TEAMEXTENSION_INSERT_FAFGROUPDIRECTHIERARCHY

          CREATE trigger TR_TEAMEXTENSION_INSERT_FAFGROUPDIRECTHIERARCHY on dbo.TEAMEXTENSION after insert not for replication
          as begin
              declare @CHANGEAGENTID uniqueidentifier,
                          @CURRENTDATE datetime

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

              ;With CTE_PARENT(TEAMID, PARENTTEAMID)
               AS
               (
                  select T.ID, T.PARENTTEAMID
                  from inserted I
                  join dbo.TEAMFUNDRAISINGTEAM T on I.TEAMFUNDRAISINGTEAMID = T.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 T.ID, T.PARENTTEAMID
                  from inserted I
                  join dbo.TEAMFUNDRAISINGTEAM T on I.TEAMFUNDRAISINGTEAMID = T.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

              -- insert self record.

              insert into dbo.FAFGROUPDIRECTHIERARCHY(ID, EVENTID, GROUPID, GROUPCONSTITUENTID, CHILDGROUPID, CHILDGROUPCONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              select newID(), I.EVENTID, I.TEAMFUNDRAISINGTEAMID, I.TEAMCONSTITUENTID, I.TEAMFUNDRAISINGTEAMID, I.TEAMCONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
              from inserted I 

          end