TR_GROUPDATA_T2BDELETE

CREATE trigger dbo.TR_GROUPDATA_T2BDELETE on dbo.GROUPDATA after delete not for replication as
begin
  if dbo.UFN_B2T_CONTEXTISSYNC() = 1
  begin
    -- fix bug 263942 - if sync is deleting groupdata record, remove any groupmember records for that household that the TA side doesn't know about

    -- bug 271579 - but not in cases where the groupdata record is being deleted due to a merge (the BBEC merge tasks will move the groupmember record to the target group)

    delete m
    from dbo.GROUPMEMBER m
    inner join DELETED d on d.ID = m.GROUPID
    where not exists(select 'x'
                     from dbo.TA_CONSTITUENT c
                     where c.ID = m.MEMBERID
                     and c.ACTIONCODE <> 1
                     and c.SYNCACTIONCODE <> 1)
    and not exists(select 'x'
                   from dbo.B2TMERGES mrg
                   where mrg.SOURCECONSTITUENTID = m.GROUPID);
  end
end