TR_GROUPMEMBER_DELETE_HOUSEHOLD_UPDATE

CREATE trigger [dbo].[TR_GROUPMEMBER_DELETE_HOUSEHOLD_UPDATE] on [dbo].[GROUPMEMBER] after delete not for replication
as
begin
  set nocount on;

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

  /*make sure you get the information from context cache if available since this is set during delete */
  exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENTFROMCONTEXT] @CHANGEAGENTID output;

  -- If the groups that the deleted entries belong to do not have a primary member, make the oldest entry in the group the primary

  -- We will only update those groups whose membership is being altered.

  update dbo.[GROUPMEMBER] set
    [ISPRIMARY] = 1,
    [DATECHANGED] = @CURRENTDATE,
    [CHANGEDBYID] = @CHANGEAGENTID
  from dbo.[GROUPMEMBER]
  inner join (
    select
      [GM].[ID],
      row_number() over(partition by [GM].[GROUPID] order by [GM].[DATEADDED]) as [ROWNUM]
    from deleted
    inner join dbo.[GROUPMEMBER] as [GM] on deleted.[GROUPID] = [GM].[GROUPID]
    left join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GM].[ID] = [GMDR].[GROUPMEMBERID]
    where deleted.[ISPRIMARY] <> 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))
  ) as [V1] on [GROUPMEMBER].[ID] = [V1].[ID] and [V1].[ROWNUM] = 1;

  update dbo.[CONSTITUENT] set
    [KEYNAME] = left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [PM].[MEMBERID]), 100),
    [DATECHANGED] = @CURRENTDATE,
    [CHANGEDBYID] = @CHANGEAGENTID
  from deleted
  inner join dbo.[GROUPDATA] on deleted.[GROUPID] = [GROUPDATA].[ID]
  inner join dbo.[GROUPMEMBER] as [PM] on deleted.[GROUPID] = [PM].[GROUPID] and [PM].[ISPRIMARY] <> 0
  where [CONSTITUENT].[ID] = deleted.[GROUPID]
  and [GROUPDATA].[NAMEFORMATFUNCTIONID] is not null
  and deleted.ISPRIMARY = 0;  -- update of group keyname upon delete of primary member is handled by TR_GROUPMEMBER_INSERT_HOUSEHOLD_UPDATE, fired after update isprimary=1 above


  --Update the constituent current household table...

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