TR_GROUPDATA_DELETE_HOUSEHOLD_UPDATE

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

  --Because the household table basically has two foreign keys back to the constituent table, we couldn't actually

  --make the HOUSEHOLDID field a foreign key that set itself to null, so we need to manually maintain the HOUSEHOLDID

  --field and set it to null when a household is deleted.

  update dbo.[CONSTITUENTHOUSEHOLD] set
    [HOUSEHOLDID] = null,
    [ISPRIMARYMEMBER] = 0
  where [HOUSEHOLDID] in (select [ID] from deleted);
end