CREATE trigger TR_ADDRESS_INSERT_SEARCHCONSTITUENT on dbo.ADDRESS after insert not for replication
as 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
|
Trigger Definition
