TR_ADDRESS_INSERT_SEARCHCONSTITUENT

        CREATE trigger TR_ADDRESS_INSERT_SEARCHCONSTITUENT on dbo.ADDRESS after insert not for replication
        as begin
          if isnull(context_info(),0x)<>0xE2DF375A033A104382689B8EAC5165AD
          begin
            -- Delete name-only rows for constituents.

            delete from dbo.SEARCHCONSTITUENT
            where CONSTITUENTID in(select CONSTITUENTID from inserted)
            and ADDRESSID is null;

            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 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