TR_CONSTITUENT_UPDATE_SEARCHCONSTITUENT
Definition
Copy
CREATE trigger TR_CONSTITUENT_UPDATE_SEARCHCONSTITUENT on dbo.CONSTITUENT after update not for replication
as begin
if isnull(context_info(),0x)<>0xE2DF375A033A104382689B8EAC5165AD
begin
if update(KEYNAME) or
update(FIRSTNAME) or
update(MIDDLENAME) or
update(TITLECODEID) or
update(SUFFIXCODEID) or
update(MAIDENNAME) or
update(NICKNAME) or
update(ISORGANIZATION) or
update(ISGROUP) or
update(ISCONSTITUENT) or
update(ISINACTIVE)
begin
if exists (select 1 from sys.TABLES where TYPE = 'U' and NAME = 'SEARCHCONSTITUENT')
begin
-- Update the constituent information for the updated names.
update s
set TITLECODEID = inserted.TITLECODEID,
FIRSTNAME = USESTRINGS.FIRSTNAME,
MIDDLENAME = USESTRINGS.MIDDLENAME,
KEYNAME = USESTRINGS.KEYNAME,
SUFFIXCODEID = inserted.SUFFIXCODEID,
ISORGANIZATION = inserted.ISORGANIZATION,
ISGROUP = inserted.ISGROUP
from inserted
inner join dbo.SEARCHCONSTITUENT s on s.CONSTITUENTID = inserted.ID
cross apply (select upper(ltrim(rtrim(replace(case s.NAMETYPECODE when 2 then inserted.NICKNAME else inserted.FIRSTNAME end,'.','')))) FIRSTNAME,
upper(ltrim(rtrim(replace(inserted.MIDDLENAME,'.','')))) MIDDLENAME,
upper(ltrim(rtrim(replace(replace(case s.NAMETYPECODE when 1 then inserted.MAIDENNAME else inserted.KEYNAME end,'.',''),'''','')))) KEYNAME) USESTRINGS
where USESTRINGS.KEYNAME<>''
and s.NAMETYPECODE <> 3
if update(ISORGANIZATION) or update(ISGROUP)
-- Update isorganization/isgroup on alias records.
update dbo.SEARCHCONSTITUENT
set ISORGANIZATION = inserted.ISORGANIZATION,
ISGROUP = inserted.ISGROUP
from inserted
where CONSTITUENTID = inserted.ID
and NAMETYPECODE = 3
-- Delete name and nickname rows where keyname is now blank.
if update(KEYNAME)
delete from dbo.SEARCHCONSTITUENT
where CONSTITUENTID in(select ID
from inserted
where ltrim(rtrim(replace(replace(inserted.KEYNAME,'.',''),'''','')))='')
and NAMETYPECODE in(0,2)
if update(MAIDENNAME) or update(NICKNAME) or update(ISCONSTITUENT) or update(ISINACTIVE)
begin
-- Delete maiden name rows where maiden name is now blank.
if update(MAIDENNAME)
delete from dbo.SEARCHCONSTITUENT
where CONSTITUENTID in(select ID
from inserted
where ltrim(rtrim(replace(replace(inserted.MAIDENNAME,'.',''),'''','')))='')
and NAMETYPECODE = 1
if update(NICKNAME)
delete from dbo.SEARCHCONSTITUENT
where CONSTITUENTID in(select ID
from inserted
where ltrim(rtrim(replace(NICKNAME,'.',''))) = '')
and NAMETYPECODE = 2
if update(ISCONSTITUENT)
delete from dbo.SEARCHCONSTITUENT
where CONSTITUENTID in(select inserted.ID
from inserted inner join deleted on inserted.ID = deleted.ID
where inserted.ISCONSTITUENT = 0 and deleted.ISCONSTITUENT = 1)
-- if a constituent becomes inactive due to a merge then exclude the record from the SEARCHCONSTITUENT table
if update(ISINACTIVE)
delete from dbo.SEARCHCONSTITUENT
where CONSTITUENTID in(select inserted.ID
from inserted inner join deleted on inserted.ID = deleted.ID
where inserted.ISINACTIVE = 1 and deleted.ISINACTIVE = 0
and inserted.ID in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS))
declare @ADDROWS table (CONSTITUENTID uniqueidentifier, NAMETYPECODE tinyint)
-- we want to record the updates of the NICKNAME, MAIDENNAME and ISCONSTITUENT to = 1 fields in the SEARCHCONSTITUENT table, however in the case when an update
-- of ISCONSTITUENT = 1, NICKNAME = 'new nickname', MAIDENNAME = 'new maiden name' happens we want to enusre that only one row is added for each of those fields
-- therefore we first insert the row for when ISCONSTITUENT is updated to 1 and set the typecode to 99 and not to insert any other rows for NICKNAME AND MAIDENNAME
-- The join clause at 556 ensures in this scenario that three rows are inserted one for each of the above fields.
-- If the ISCONSTITUENT is not updated then we need the second insert to handle the updates of NICKNAME and CONSTITUENT
-- Same logic applies for when when a constituent that was the source of a merge is reactivated
-- handle updates of ISCONSTITUENT and ISINACTIVE (via merge)
insert into @ADDROWS
select inserted.ID, 99
from inserted
inner join deleted on deleted.id = inserted.id
where (inserted.isconstituent = 1 and deleted.isconstituent = 0) -- non-constituent becomes a constituent
or (inserted.isinactive = 0 and deleted.isinactive = 1 and inserted.ID in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)) -- an inactive merge constituent is reactivated
-- handle updates to NICKNAME and MAIDENNAME if the previous insert didn't fire
insert into @ADDROWS
select inserted.ID, 1
from inserted
inner join deleted on deleted.id = inserted.id
where inserted.MAIDENNAME <> ''
and deleted.MAIDENNAME = ''
and inserted.ID not in (select CONSTITUENTID from @ADDROWS)
union all
select inserted.ID, 2
from inserted
inner join deleted on deleted.id = inserted.id
where inserted.NICKNAME <> ''
and deleted.NICKNAME = ''
and inserted.ID not in (select CONSTITUENTID from @ADDROWS)
insert into dbo.SEARCHCONSTITUENT (ID,CONSTITUENTID,ADDRESSID,
KEYNAME,FIRSTNAME,MIDDLENAME,NAMETYPECODE,
TITLECODEID,SUFFIXCODEID,
COUNTRYID,POSTCODE,STREETNUMBER,STREETNAME,
ISORGANIZATION,ISGROUP)
select newid(),
c.ID,
a.ID,
c.KEYNAME,
c.FIRSTNAME,
c.MIDDLENAME,
c.NAMETYPECODE,
c.TITLECODEID,
c.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,ab.CLEANADDRESSBLOCK),''),
c.ISORGANIZATION,
c.ISGROUP
from @ADDROWS ar
inner join dbo.V_CONSTITUENTALLNAMES c on c.ID = ar.CONSTITUENTID and (c.NAMETYPECODE = ar.NAMETYPECODE or ar.NAMETYPECODE = 99)
left outer join ADDRESS a on a.CONSTITUENTID = c.ID
outer 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),' '),' ',' ')))) 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
end
end
end