TR_GROUPMEMBER_INSERT_HOUSEHOLD_UPDATE (Trigger)

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

    if update([ISPRIMARY])
    begin
        declare @CHANGEAGENTID uniqueidentifier;
        declare @CURRENTDATE datetime = getdate();

        -- 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 @HOUSEHOLDNAME nvarchar(100);
        declare @HASNAMEFORMATFUNCTION bit = 0;
        select
            @HOUSEHOLDNAME = left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [GROUPMEMBER].[MEMBERID]), 100),
            @HASNAMEFORMATFUNCTION = 1
        from inserted
            inner join dbo.[GROUPDATA] on inserted.[GROUPID] = [GROUPDATA].[ID]
            inner join dbo.[GROUPMEMBER] on inserted.[GROUPID] = [GROUPMEMBER].[GROUPID]
        where [GROUPDATA].[NAMEFORMATFUNCTIONID] is not null and [GROUPMEMBER].[ISPRIMARY] <> 0;

        if @HASNAMEFORMATFUNCTION = 1
        begin
            if @HOUSEHOLDNAME is null or @HOUSEHOLDNAME = ''
            begin
                declare @FALLBACKHOUSEHOLDNAMEFORMATID uniqueidentifier
                select @FALLBACKHOUSEHOLDNAMEFORMATID = ID
                from dbo.NAMEFORMATFUNCTION 
                where FORMATSQLFUNCTION = 'UFN_NAMEFORMAT_32';
        
                select
                    @HOUSEHOLDNAME = left(dbo.[UFN_NAMEFORMAT_FROMID](@FALLBACKHOUSEHOLDNAMEFORMATID, [GROUPMEMBER].[MEMBERID]), 100)
                from inserted
                    inner join dbo.[GROUPMEMBER] on inserted.[GROUPID] = [GROUPMEMBER].[GROUPID]
                where [GROUPMEMBER].[ISPRIMARY] <> 0;
            end

            update dbo.[CONSTITUENT] 
            set
                [KEYNAME] = @HOUSEHOLDNAME,
                [DATECHANGED] = @CURRENTDATE,
                [CHANGEDBYID] = isnull(@CHANGEAGENTID, inserted.[CHANGEDBYID])
            from inserted
                inner join dbo.[GROUPMEMBER] on inserted.[GROUPID] = [GROUPMEMBER].[GROUPID]
            where [CONSTITUENT].[ID] = inserted.[GROUPID] and [GROUPMEMBER].[ISPRIMARY] <> 0;
        end
    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]
    where [GROUPDATA].[GROUPTYPECODE] = 0
        and not exists(select 1 from dbo.[CONSTITUENTHOUSEHOLD] where [ID] = inserted.[MEMBERID]);

    --Update the constituent current household table...

    update dbo.[CONSTITUENTHOUSEHOLD] 
    set
        [HOUSEHOLDID] = inserted.[GROUPID],
        [ISPRIMARYMEMBER] = inserted.[ISPRIMARY]
    from inserted
        inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = inserted.[GROUPID]
        inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = inserted.[MEMBERID]
    where [GROUPDATA].[GROUPTYPECODE] = 0;
end