TR_ALIAS_INSERT_SEARCHCONSTITUENT

        CREATE trigger TR_ALIAS_INSERT_SEARCHCONSTITUENT on dbo.ALIAS after insert not for replication
        as begin
          if isnull(context_info(),0x)<>0xE2DF375A033A104382689B8EAC5165AD
          begin
            declare @NULLID uniqueidentifier = newid()

            insert into dbo.SEARCHCONSTITUENT (ID,CONSTITUENTID,ADDRESSID,
                                               KEYNAME,FIRSTNAME,MIDDLENAME,NAMETYPECODE,
                                               TITLECODEID,SUFFIXCODEID,
                                               COUNTRYID,POSTCODE,STREETNUMBER,STREETNAME,
                                               ISORGANIZATION,ISGROUP,ALIASID)
            select newid(),
                   c.ID,
                   a.ID,
                   CLEANSTRINGS.ALIASKEYNAME,
                   CLEANSTRINGS.ALIASFIRSTNAME,
                   CLEANSTRINGS.ALIASMIDDLENAME,
                   3,
                   inserted.TITLECODEID,
                   inserted.SUFFIXCODEID,
                   a.COUNTRYID,
                   case when ltrim(rtrim(a.POSTCODE)) like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then left(ltrim(a.POSTCODE),5) else isnull(ltrim(rtrim(a.POSTCODE)),'') end,
                   isnull(PARSEDADDRESS.STREETNUMBER,''),
                   isnull(isnull(PARSEDADDRESS.STREETNAME,CLEANSTRINGS.ADDRESSBLOCK),''),
                   c.ISORGANIZATION,
                   c.ISGROUP,
                   inserted.ID
            from inserted
            inner join dbo.SEARCHCONSTITUENTALIASTYPE on SEARCHCONSTITUENTALIASTYPE.ID = inserted.ALIASTYPECODEID
            inner join dbo.CONSTITUENT c on c.ID = inserted.CONSTITUENTID
            left outer join dbo.ADDRESS a on a.CONSTITUENTID = c.ID
            cross apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
                                 a.ADDRESSBLOCK,'.',''),'-',' '),',',' '),
                                 char(13)+char(10),' '),char(10)+char(13),' '),char(13),' '),char(10),' '),'  ',' ')))) ADDRESSBLOCK,
                                 upper(ltrim(rtrim(replace(inserted.FIRSTNAME,'.','')))) ALIASFIRSTNAME,
                                 upper(ltrim(rtrim(replace(inserted.MIDDLENAME,'.','')))) ALIASMIDDLENAME,
                                 upper(ltrim(rtrim(replace(replace(inserted.KEYNAME,'.',''),'''','')))) ALIASKEYNAME,
                                 upper(ltrim(rtrim(replace(c.FIRSTNAME,'.','')))) CONSTITUENTFIRSTNAME,
                                 upper(ltrim(rtrim(replace(c.MIDDLENAME,'.','')))) CONSTITUENTMIDDLENAME,
                                 upper(ltrim(rtrim(replace(replace(c.KEYNAME,'.',''),'''','')))) CONSTITUENTKEYNAME) CLEANSTRINGS
            outer apply (select left(CLEANSTRINGS.ADDRESSBLOCK,charindex(' ',CLEANSTRINGS.ADDRESSBLOCK)-1) STREETNUMBER,
                                right(CLEANSTRINGS.ADDRESSBLOCK,len(CLEANSTRINGS.ADDRESSBLOCK)-charindex(' ',CLEANSTRINGS.ADDRESSBLOCK)) STREETNAME
                         where dbo.UFN_REGULAREXPRESSIONMATCH('(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)',CLEANSTRINGS.ADDRESSBLOCK) = 1
                         and charindex(' ',CLEANSTRINGS.ADDRESSBLOCK)<=13) PARSEDADDRESS
            where CLEANSTRINGS.ALIASKEYNAME<>''
            and (inserted.TITLECODEID<>isnull(c.TITLECODEID,@NULLID) or
                 (CLEANSTRINGS.ALIASFIRSTNAME<>CLEANSTRINGS.CONSTITUENTFIRSTNAME and CLEANSTRINGS.ALIASFIRSTNAME<>'') or
                 (CLEANSTRINGS.ALIASMIDDLENAME<>CLEANSTRINGS.CONSTITUENTMIDDLENAME and CLEANSTRINGS.ALIASMIDDLENAME<>'') or
                 CLEANSTRINGS.ALIASKEYNAME<>CLEANSTRINGS.CONSTITUENTKEYNAME or
                 inserted.SUFFIXCODEID<>isnull(c.SUFFIXCODEID,@NULLID))
            and c.ISCONSTITUENT = 1
            and (c.ISINACTIVE = 0 or (c.ISINACTIVE = 1 and c.ID not in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)))    
          end
        end