CREATE trigger TR_EMAILADDRESS_TASYNC_I on dbo.EMAILADDRESS after insert not for replication
as begin
declare @CHILDRECORDS table (EMAILADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, ISPRIMARY bit);
if dbo.UFN_B2T_CONTEXTISSYNC() = 0
begin
insert into dbo.TA_EMAILADDRESS (ID, SYNCID, ACTIONCODE)
select ID, newid(), 1
from INSERTED
-- don't sync records for groups
where not exists(select 'x'
from dbo.GROUPDATA d
where d.ID = INSERTED.CONSTITUENTID
and d.GROUPTYPECODE = 1);
--
-- note that the above insert could fail if row is deleted and then reinserted w/ same ID before sync is run
-- since we would not know anything about whether the row is really the same or not, we need to throw an exception
-- to resolve, run the sync, then retry the insert
--
-- find and store all the records that looks the same as the record being inserted.
--
insert into @CHILDRECORDS (EMAILADDRESSID, CONSTITUENTID, ISPRIMARY)
select a.ID, a.CONSTITUENTID, a.ISPRIMARY
from INSERTED i
inner join dbo.GROUPMEMBER m on m.GROUPID = i.CONSTITUENTID
inner join dbo.EMAILADDRESS a on a.CONSTITUENTID = m.MEMBERID
and (a.EMAILADDRESS = i.EMAILADDRESS
and (a.EMAILADDRESSTYPECODEID = i.EMAILADDRESSTYPECODEID
or (a.EMAILADDRESSTYPECODEID is null and i.EMAILADDRESSTYPECODEID is null)))
union all
select a2.ID, a2.CONSTITUENTID, a2.ISPRIMARY
from INSERTED i
inner join dbo.RELATIONSHIP r on i.CONSTITUENTID=r.RELATIONSHIPCONSTITUENTID
inner join dbo.EMAILADDRESS a2 on a2.RELATIONSHIPID=r.ID
where r.ISCONTACT=1
and (a2.EMAILADDRESS = i.EMAILADDRESS
and (a2.EMAILADDRESSTYPECODEID = i.EMAILADDRESSTYPECODEID
or (a2.EMAILADDRESSTYPECODEID is null and i.EMAILADDRESSTYPECODEID is null)));
--
-- flag additional records for when primary is inserted and the old primary for the
-- accounts has to be unflagged. This is to handle the case where the inserted record
-- is the first one for the household and a member's primary record was synced as
-- the account's primary in TA. This record now has to be unflagged as the account's
-- primary.
--
update t
set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
from dbo.TA_EMAILADDRESS t, INSERTED i
where t.ID = dbo.UFN_B2T_GETACCOUNTPREFERREDEMAIL(i.CONSTITUENTID,1)
and i.ISPRIMARY = 1
--
-- When inserting a household address that matches a member's emailaddress, flag the member's
-- row for update, in this case the member's row will be deleted in TA and will be associated
-- with the household's emailaddress.
--
update dbo.TA_EMAILADDRESS
set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
where ID in (select EMAILADDRESSID from @CHILDRECORDS)
--
-- flag the members in the household for update when a primary record is inserted
-- and the constituent does not have a record or if the record matches the inserted record
-- or the record being inserted is considered the household's primary
--
update t
set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
from dbo.TA_CONSTITUENT t
inner join INSERTED i on i.CONSTITUENTID = t.ID
where i.ISPRIMARY = 1
update t
set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
from INSERTED i
cross apply dbo.UFN_B2T_GET_HOUSEHOLDANDMEMBERS(i.CONSTITUENTID, 1) m
inner join dbo.TA_CONSTITUENT t on t.ID = m.MEMBERID
where i.ISPRIMARY = 1
and NOT exists (select 'x' from dbo.EMAILADDRESS a where t.ID = a.CONSTITUENTID)
--
--flag the contact(s) for an org constituent if that contact does not have an address
--
update t
set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
from INSERTED i
inner join dbo.RELATIONSHIP r on i.CONSTITUENTID = r.RELATIONSHIPCONSTITUENTID
inner join dbo.TA_CONSTITUENT t on t.ID = r.RECIPROCALCONSTITUENTID
where dbo.UFN_CONSTITUENT_ISORGANIZATION (i.CONSTITUENTID) = 1
and i.ISPRIMARY = 1
and NOT exists (select 'x' from dbo.EMAILADDRESS a where t.ID = a.CONSTITUENTID)
--
-- flag constituents when inserting a master to couple to a household master. This is to update
-- the primary emailaddress IDs for the name record.
--
update dbo.TA_CONSTITUENT
set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
where ID in (select CONSTITUENTID from @CHILDRECORDS where ISPRIMARY = 1);
update t
set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
from dbo.TA_NAMES0 t
inner join INSERTED i on i.CONSTITUENTID = t.ID and i.ISPRIMARY = 1
-- Refresh accounts.address_sts
update dbo.TA_ACCOUNTS
set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
where ID in(select CONSTITUENTID from INSERTED);
end
end
