TR_GROUPMEMBER_INSERT_HOUSEHOLD_UPDATE

CREATE trigger [dbo].[TR_GROUPMEMBER_INSERT_HOUSEHOLD_UPDATE] on [dbo].[GROUPMEMBER] after insert, update not for replication
as
begin
    set nocount on;

  declare @CURRENTDATE datetime = getdate();
  declare @CURRENTDATEEARLIESTTIME date = @CURRENTDATE;

    if update([ISPRIMARY])
    begin
        declare @CHANGEAGENTID uniqueidentifier;

        -- For audit reasons:

        -- Use the same ChangeAgentId used to update the groupmember row if it was updated.

        -- If null, the set will simply coalesce out and effectively do nothing

        -- Since this trigger may be hit by a bulk update, we can not assume that all ChangeByIds are the same for all records unless they aren't being specified.

        if update([CHANGEDBYID])
            set @CHANGEAGENTID = null;
        else
            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        declare @FALLBACKHOUSEHOLDNAMEFORMATID uniqueidentifier
        select @FALLBACKHOUSEHOLDNAMEFORMATID = ID
        from dbo.NAMEFORMATFUNCTION 
        where FORMATSQLFUNCTION = 'UFN_NAMEFORMAT_32';

        update dbo.[CONSTITUENT] 
        set
            [KEYNAME] =
        -- Determine whether or not to use name function or fallback function

        case coalesce(left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [GROUPMEMBER].[MEMBERID]), 100),'')
          when '' then left(dbo.[UFN_NAMEFORMAT_FROMID](@FALLBACKHOUSEHOLDNAMEFORMATID, [GROUPMEMBER].[MEMBERID]), 100)
          else left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [GROUPMEMBER].[MEMBERID]), 100)
        end,
            [DATECHANGED] = @CURRENTDATE,
            [CHANGEDBYID] = isnull(@CHANGEAGENTID, inserted.[CHANGEDBYID])
        from inserted
            inner join dbo.[GROUPDATA] on inserted.[GROUPID] = [GROUPDATA].[ID]
            inner join dbo.[GROUPMEMBER] on inserted.[GROUPID] = [GROUPMEMBER].[GROUPID]
        where
      [CONSTITUENT].[ID] = inserted.[GROUPID] and
      [GROUPDATA].[NAMEFORMATFUNCTIONID] is not null and
      [GROUPMEMBER].[ISPRIMARY] <> 0;
    end

  -- insert new members into the constituent current household table

  insert into dbo.[CONSTITUENTHOUSEHOLD] ([ID], [HOUSEHOLDID], [ISPRIMARYMEMBER])
    select
      inserted.[MEMBERID],
      inserted.[GROUPID],
      inserted.[ISPRIMARY]
    from inserted
    inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = inserted.[GROUPID]
    left outer join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GMDR].[GROUPMEMBERID] = inserted.[ID]
    where [GROUPDATA].[GROUPTYPECODE] = 0
    and not exists (select 1 from dbo.[CONSTITUENTHOUSEHOLD] where [ID] = inserted.[MEMBERID])
    and (([GMDR].[DATEFROM] is null and ([GMDR].[DATETO] is null or [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
      or ([GMDR].[DATETO] is null and ([GMDR].[DATEFROM] is null or [GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME)) 
      or ([GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME and [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME));

  -- update the constituent current household table with information about households that are active

  -- (i.e. today's date falls within the date range)  

  update dbo.[CONSTITUENTHOUSEHOLD] set
    [HOUSEHOLDID] = inserted.[GROUPID],
    [ISPRIMARYMEMBER] = inserted.[ISPRIMARY]
  from inserted
  inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = inserted.[GROUPID]
  left outer join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GMDR].[GROUPMEMBERID] = inserted.[ID]
  inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = inserted.[MEMBERID]
  where [GROUPDATA].[GROUPTYPECODE] = 0
  and (([GMDR].[DATEFROM] is null and ([GMDR].[DATETO] is null or [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
    or ([GMDR].[DATETO] is null and ([GMDR].[DATEFROM] is null or [GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME)) 
    or ([GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME and [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME));
end