TR_TEAMEXTENSION_UPDATE_TEAMCONSTITUENT (Trigger)

        
          create trigger TR_TEAMEXTENSION_UPDATE_TEAMCONSTITUENT on dbo.TEAMEXTENSION after update not for replication
          as begin
          
                  if update(TEAMCONSTITUENTID) OR update(TYPECODE)
                  begin
          
              declare @CHANGEAGENTID uniqueidentifier,
                      @CURRENTDATE datetime

              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
              set @CURRENTDATE = GETDATE()
                  
              update dbo.FAFGROUPSUMMARYINFORMATION
              set GROUPCONSTITUENTID = I.TEAMCONSTITUENTID,
                  GROUPTYPECODE = I.TYPECODE,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED   = @CURRENTDATE
              from  inserted I   
              join dbo.FAFGROUPSUMMARYINFORMATION FG on I.TEAMFUNDRAISINGTEAMID = FG.ID
                
              if update(TEAMCONSTITUENTID)
              begin   
                  update dbo.FAFGROUPDIRECTHIERARCHY
                  set GROUPCONSTITUENTID = I.TEAMCONSTITUENTID,   
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED   = @CURRENTDATE 
                  from inserted I
                  join dbo.FAFGROUPDIRECTHIERARCHY FG on I.TEAMFUNDRAISINGTEAMID = FG.GROUPID 
                 
                  update dbo.FAFGROUPDIRECTHIERARCHY
                  set CHILDGROUPCONSTITUENTID = I.TEAMCONSTITUENTID,   
                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED   = @CURRENTDATE 
                  from inserted I
                  join dbo.FAFGROUPDIRECTHIERARCHY FG on I.TEAMFUNDRAISINGTEAMID = FG.CHILDGROUPID 
                 
              end                  
                  end
          end