TR_ADDRESS_UPDATE_SEARCHCONSTITUENT

        CREATE trigger TR_ADDRESS_UPDATE_SEARCHCONSTITUENT on dbo.ADDRESS after update not for replication
        as begin
          if isnull(context_info(),0x)<>0xE2DF375A033A104382689B8EAC5165AD
          begin
            if update(CONSTITUENTID)
            begin
              -- Remove rows for old constituents.

              delete from dbo.SEARCHCONSTITUENT
              where ADDRESSID in(select d.ID
                                 from deleted d, inserted i
                                 where d.ID = i.ID
                                 and d.CONSTITUENTID <> i.CONSTITUENTID)

            if @@rowcount > 0
            begin
              -- If there are no more addresses for the old constituent, add a name-only row.

              declare @DELETEDCONSTITS table (CONSTITUENTID uniqueidentifier)

              -- Load the distinct deleted constituents into a temp table.

              -- Originally I had this in-line with the insert below but it caused performance issues.

              -- Taking it out to a temp table resolved this.

              insert into @DELETEDCONSTITS
              select distinct d.CONSTITUENTID
              from deleted d, inserted i
              where d.ID = i.ID
              and d.CONSTITUENTID <> i.CONSTITUENTID;

              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 @DELETEDCONSTITS 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);

              -- Delete name-only rows for constituents for which we are adding addresses.

              delete from dbo.SEARCHCONSTITUENT
              where CONSTITUENTID in(select i.CONSTITUENTID
                                     from deleted d, inserted i
                                     where d.ID = i.ID
                                     and d.CONSTITUENTID <> i.CONSTITUENTID)
              and ADDRESSID is null;

              -- Add rows for new constituents.

              insert into dbo.SEARCHCONSTITUENT (ID,CONSTITUENTID,ADDRESSID,
                                                 KEYNAME,FIRSTNAME,MIDDLENAME,NAMETYPECODE,
                                                 TITLECODEID,SUFFIXCODEID,
                                                 COUNTRYID,POSTCODE,STREETNUMBER,STREETNAME,
                                                 ISORGANIZATION,ISGROUP,ALIASID)
              select newid(),
                     c.ID,
                     inserted.ID,
                     c.KEYNAME,
                     c.FIRSTNAME,
                     c.MIDDLENAME,
                     c.NAMETYPECODE,
                     c.TITLECODEID,
                     c.SUFFIXCODEID,
                     inserted.COUNTRYID,
                     case when ltrim(rtrim(inserted.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then left(ltrim(inserted.POSTCODE),5) else ltrim(rtrim(inserted.POSTCODE)) end,
                     isnull(PARSEDADDRESS.STREETNUMBER,''),
                     isnull(PARSEDADDRESS.STREETNAME,ab.CLEANADDRESSBLOCK),
                     c.ISORGANIZATION,
                     c.ISGROUP,
                     c.ALIASID
              from inserted
              inner join deleted on deleted.ID = inserted.ID and deleted.CONSTITUENTID <> inserted.CONSTITUENTID
              inner join dbo.V_CONSTITUENTALLNAMES c on c.ID = inserted.CONSTITUENTID
              cross apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
                                   inserted.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
                                   char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),'  ',' ')))) CLEANADDRESSBLOCK) ab
              outer apply (select left(CLEANADDRESSBLOCK,charindex(' ',CLEANADDRESSBLOCK)-1) STREETNUMBER,
                                  right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex(' ',CLEANADDRESSBLOCK)) STREETNAME
                           where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANADDRESSBLOCK) = 1
                           and charindex(' ',CLEANADDRESSBLOCK)<=13) PARSEDADDRESS
              where c.KEYNAME<>''
            end
          end

                      if update(ADDRESSBLOCK) or update(POSTCODE) or update(COUNTRYID)
            begin
              update dbo.SEARCHCONSTITUENT
              set COUNTRYID = inserted.COUNTRYID,
                  POSTCODE = case when ltrim(rtrim(inserted.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then left(ltrim(inserted.POSTCODE),5) else ltrim(rtrim(inserted.POSTCODE)) end,
                  STREETNUMBER = isnull(PARSEDADDRESS.STREETNUMBER,''),
                  STREETNAME = isnull(PARSEDADDRESS.STREETNAME,ab.CLEANADDRESSBLOCK)
              from inserted
              cross apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
                                   inserted.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
                                   char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),'  ',' ')))) CLEANADDRESSBLOCK) ab
              outer apply (select left(CLEANADDRESSBLOCK,charindex(' ',CLEANADDRESSBLOCK)-1) STREETNUMBER,
                                  right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex(' ',CLEANADDRESSBLOCK)) STREETNAME
                           where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANADDRESSBLOCK) = 1
                           and charindex(' ',CLEANADDRESSBLOCK)<=13) PARSEDADDRESS
              where SEARCHCONSTITUENT.ADDRESSID = inserted.ID
            end
          end
        end