CREATE trigger TR_CONSTITUENT_UPDATE_SEARCHCONSTITUENT on dbo.CONSTITUENT after update not for replication
as 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 @NULLID uniqueidentifier = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)
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
|
Trigger Definition
