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