TR_GROUPMEMBERDATERANGE_UPDATE_HOUSEHOLD_UPDATE

CREATE trigger [dbo].[TR_GROUPMEMBERDATERANGE_UPDATE_HOUSEHOLD_UPDATE] on [dbo].[GROUPMEMBERDATERANGE] after update not for replication
as
begin
  set nocount on;

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

    -- For audit reasons:

    -- Use the same ChangeAgentId used to update the groupmemberdaterange 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;

    -- Find groups which the updated constituents are members of

    declare @GROUPS table(GROUPID uniqueidentifier, HASPRIMARYCONTACT bit);
    insert into @GROUPS(GROUPID, HASPRIMARYCONTACT)
    select
        [GROUPMEMBER].[GROUPID],
        0
    from
        dbo.[GROUPMEMBER]
        inner join inserted on inserted.GROUPMEMBERID = [GROUPMEMBER].ID
    group by
        [GROUPMEMBER].[GROUPID]

    -- determine which groups have active primary members

    update @GROUPS
    set
        HASPRIMARYCONTACT = 1
    from
        @GROUPS [GROUPS]
        inner join dbo.[GROUPMEMBER] on [GROUPMEMBER].[GROUPID] = [GROUPS].[GROUPID]
        inner join dbo.[GROUPMEMBERDATERANGE] on [GROUPMEMBER].[ID] = [GROUPMEMBERDATERANGE].[GROUPMEMBERID]
    where
        [GROUPMEMBER].[ISPRIMARY] = 1
        and ([GROUPMEMBERDATERANGE].[DATETO] is null or ([GROUPMEMBERDATERANGE].[DATETO] is not null and [GROUPMEMBERDATERANGE].[DATETO] > @CURRENTDATE))

    -- if a group does not have an active primary member, set the current inactive primary member to non-primary

    update dbo.[GROUPMEMBER]
    set
        [GROUPMEMBER].[ISPRIMARY] = 0,
        [GROUPMEMBER].[DATECHANGED] = @CURRENTDATE,
        [GROUPMEMBER].[CHANGEDBYID] = isnull(@CHANGEAGENTID, [GROUPMEMBER].CHANGEDBYID)
    from
        @GROUPS [GROUPS]
        inner join dbo.[GROUPMEMBER] on [GROUPS].[GROUPID] = [GROUPMEMBER].[GROUPID]
        inner join dbo.[GROUPMEMBERDATERANGE] on [GROUPMEMBER].[ID] = [GROUPMEMBERDATERANGE].[GROUPMEMBERID]
    where
        [GROUPMEMBER].[ISPRIMARY] = 1
        and [GROUPMEMBERDATERANGE].[DATETO] is not null and [GROUPMEMBERDATERANGE].[DATETO] <= @CURRENTDATE

    -- if a group does not have an active primary member, set the oldest (by DATEADDED) current member as primary

    update dbo.[GROUPMEMBER]
    set
        [GROUPMEMBER].[ISPRIMARY] = 1,
        [GROUPMEMBER].[DATECHANGED] = @CURRENTDATE,
        -- if change agent was not supplied for an inserted record, use the one retrieved from USP_CHANGEAGENT_GETORCREATECHANGEAGENT

        -- if a change agent was supplied for an inserted record, and a group member corresponding to that record is being updated, use the supplied change agent

        -- if there is a change agent specified but not for this group member, use a change agent from one of the inserted records in the same group

        [GROUPMEMBER].[CHANGEDBYID] = coalesce(@CHANGEAGENTID, inserted.CHANGEDBYID, (
            select top 1
                inserted.[CHANGEDBYID]
            from
                inserted
                inner join dbo.[GROUPMEMBER] [IGO] on [IGO].[ID] = inserted.[GROUPMEMBERID]
            where
                [IGO].[GROUPID] = [GROUPMEMBER].[GROUPID]
            order by
                inserted.[GROUPMEMBERID]
        ))
    from
        dbo.[GROUPMEMBER]
        -- get the most senior current record

        inner join (
            select
                [GM].[ID],
                [GM].[ISPRIMARY],
                [ROWNUM] = row_number() over(partition by [GM].[GROUPID] order by [GM].[ISPRIMARY] desc, [GM].[DATEADDED] asc)
            from
                @GROUPS [GROUPS]
                inner join dbo.[GROUPMEMBER] [GM] on [GM].[GROUPID] = [GROUPS].[GROUPID]
                inner join dbo.[GROUPMEMBERDATERANGE] [GMDR] on [GMDR].[GROUPMEMBERID] = [GM].[ID]
            where
                (([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))
        ) [V1] on [GROUPMEMBER].[ID] = [V1].[ID] and [V1].[ROWNUM] = 1 and [V1].[ISPRIMARY] = 0
        left join inserted on [GROUPMEMBER].[ID] = inserted.[GROUPMEMBERID]

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

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

    update dbo.[CONSTITUENTHOUSEHOLD] set
        [HOUSEHOLDID] = [GROUPMEMBER].[GROUPID],
        [ISPRIMARYMEMBER] = [GROUPMEMBER].[ISPRIMARY]
    from
        dbo.[GROUPMEMBER]
        inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = [GROUPMEMBER].[GROUPID]
        inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = [GROUPMEMBER].[MEMBERID]
        left outer join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GMDR].[GROUPMEMBERID] = [GROUPMEMBER].[ID]
    where
        (([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))
        and [GROUPDATA].[GROUPTYPECODE] = 0
        and exists(
            select [ID]
            from inserted
            where
                [GROUPMEMBER].[ID] = inserted.[GROUPMEMBERID]
            );

    -- update the constituent current household table for households that are dissolved / not active

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

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