TR_ADDRESS_DELETE_SEARCHCONSTITUENT

        CREATE trigger TR_ADDRESS_DELETE_SEARCHCONSTITUENT on dbo.ADDRESS after delete not for replication
        as begin
          if isnull(context_info(),0x)<>0xE2DF375A033A104382689B8EAC5165AD
          begin
            delete from dbo.SEARCHCONSTITUENT
            where ADDRESSID in(select ID from deleted);

            -- add name-only row to searchconstituent for constituent where no more addresses remain

            insert into dbo.SEARCHCONSTITUENT (ID,CONSTITUENTID,
                                                KEYNAME,FIRSTNAME,MIDDLENAME,NAMETYPECODE,
                                                TITLECODEID,SUFFIXCODEID,
                                                ISORGANIZATION,ISGROUP, ALIASID)
            select newid(),
                   d.CONSTITUENTID,
                   c.KEYNAME,
                   c.FIRSTNAME,
                   c.MIDDLENAME,
                   c.NAMETYPECODE,
                   c.TITLECODEID,
                   c.SUFFIXCODEID,
                   c.ISORGANIZATION,
                   c.ISGROUP,
                   c.ALIASID
            from (select distinct CONSTITUENTID from deleted) d
            inner join dbo.V_CONSTITUENTALLNAMES c on c.ID = d.CONSTITUENTID
            where c.KEYNAME<>''
            and not exists(select 'x' from dbo.SEARCHCONSTITUENT where CONSTITUENTID = d.CONSTITUENTID)
          end
        end