CREATE trigger TR_ALIAS_INSERT_SEARCHCONSTITUENT on dbo.ALIAS after insert not for replication
as 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))
end
|
Trigger Definition
