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;

      update dbo.[CONSTITUENT] set
        [KEYNAME] = left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [PM].[MEMBERID]), 100),
        [DATECHANGED] = @CURRENTDATE,
        [CHANGEDBYID] = isnull(@CHANGEAGENTID, inserted.[CHANGEDBYID])
      from inserted
      inner join dbo.[GROUPDATA] on inserted.[GROUPID] = [GROUPDATA].[ID]
      inner join dbo.[GROUPMEMBER] as [PM] on inserted.[GROUPID] = [PM].[GROUPID] and [PM].[ISPRIMARY] <> 0
      where [CONSTITUENT].[ID] = inserted.[GROUPID]
      and [GROUPDATA].[NAMEFORMATFUNCTIONID] is not null;
    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