CREATE trigger TR_ALIAS_UPDATE_SEARCHCONSTITUENT on dbo.ALIAS after update not for replication
as begin
if update(CONSTITUENTID)
begin
delete from dbo.SEARCHCONSTITUENT
where ALIASID in(select d.ID
from deleted d, inserted i
where d.ID = i.ID
and d.CONSTITUENTID <> i.CONSTITUENTID)
if @@rowcount > 0
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 ltrim(rtrim(a.POSTCODE)) end,
isnull(PARSEDADDRESS.STREETNUMBER,''),
isnull(PARSEDADDRESS.STREETNAME,CLEANSTRINGS.ADDRESSBLOCK),
c.ISORGANIZATION,
c.ISGROUP,
inserted.ID
from inserted
inner join deleted on deleted.ID = inserted.ID and deleted.CONSTITUENTID <> inserted.CONSTITUENTID
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
end
if update(TITLECODEID) or
update(FIRSTNAME) or
update(KEYNAME) or
update(MIDDLENAME) or
update(SUFFIXCODEID)
begin
update sc
set TITLECODEID = inserted.TITLECODEID,
FIRSTNAME = upper(ltrim(rtrim(replace(inserted.FIRSTNAME,'.','')))),
MIDDLENAME = upper(ltrim(rtrim(replace(inserted.MIDDLENAME,'.','')))),
KEYNAME = upper(ltrim(rtrim(replace(replace(inserted.KEYNAME,'.',''),'''','')))),
SUFFIXCODEID = inserted.SUFFIXCODEID
from inserted
inner join dbo.SEARCHCONSTITUENT sc on sc.ALIASID = inserted.ID
inner join deleted on deleted.id = inserted.id
where inserted.CONSTITUENTID = deleted.CONSTITUENTID
and ltrim(rtrim(replace(replace(inserted.KEYNAME,'.',''),'''','')))<>''
-- Delete alias rows where keyname is now blank.
if update(KEYNAME)
delete from dbo.SEARCHCONSTITUENT
where ALIASID in(select ID
from inserted
where ltrim(rtrim(replace(replace(inserted.KEYNAME,'.',''),'''','')))='')
end
if update(ALIASTYPECODEID)
begin
delete from dbo.SEARCHCONSTITUENT
where ALIASID in(select ID from inserted where ALIASTYPECODEID not in(select ID from dbo.SEARCHCONSTITUENTALIASTYPE) or ALIASTYPECODEID 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,
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 not exists(select 'x' from dbo.SEARCHCONSTITUENT where ALIASID = inserted.ID);
end
end
|
Trigger Definition
