TR_ADDRESS_DELETE_SEARCHCONSTITUENT (Trigger)

        
        CREATE trigger TR_ADDRESS_DELETE_SEARCHCONSTITUENT on dbo.ADDRESS after delete not for replication
        as begin
          -- 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)
          select newid(),
                 d.CONSTITUENTID,
                 c.KEYNAME,
                 c.FIRSTNAME,
                 c.MIDDLENAME,
                 c.NAMETYPECODE,
                 c.TITLECODEID,
                 c.SUFFIXCODEID,
                 c.ISORGANIZATION,
                 c.ISGROUP
          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