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)
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)
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
declare @ADDROWS table (CONSTITUENTID uniqueidentifier, NAMETYPECODE tinyint)
insert into @ADDROWS
select inserted.ID, 1
from inserted
inner join deleted on deleted.id = inserted.id
where inserted.MAIDENNAME <> ''
and deleted.MAIDENNAME = ''
union all
select inserted.ID, 2
from inserted
inner join deleted on deleted.id = inserted.id
where inserted.NICKNAME <> ''
and deleted.NICKNAME = ''
if @@rowcount > 0
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
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
|
Trigger Definition
