CREATE trigger TR_CONSTITUENTNOTE_TASYNC_U on dbo.CONSTITUENTNOTE after update not for replication
as begin
if dbo.UFN_B2T_CONTEXTISSYNC() = 0
begin
-- normally this is triggering an update of interactions
-- if notetypecode changed from one not mapped to interactions to one that is, this triggers an insert instead
merge dbo.TA_CONSTITUENTNOTE t
using (select i.ID
from INSERTED i
-- only keep notes using codes mapped from interactions
inner join dbo.B2TROWS as jcontact_type on jcontact_type.TATABLE = 'CONTACT_TYPE' and jcontact_type.BBECTABLE = 'CONSTITUENTNOTETYPECODE' and jcontact_type.BBECID = i.CONSTITUENTNOTETYPECODEID) u
on (t.ID = u.ID)
when matched then
update set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
when not matched then
insert (ID, SYNCID, ACTIONCODE) values (u.ID, newid(), 1);
-- if notetypecode changed to one not mapped to interactions, delete the interaction from TA
merge dbo.TA_CONSTITUENTNOTE t
using (select i.ID
from INSERTED i
where not exists(select 'x'
from dbo.B2TROWS jcontact_type
where jcontact_type.TATABLE = 'CONTACT_TYPE'
and jcontact_type.BBECTABLE = 'CONSTITUENTNOTETYPECODE'
and jcontact_type.BBECID = i.CONSTITUENTNOTETYPECODEID)) u
on (t.ID = u.ID)
when matched and t.ACTIONCODE = 1 then
delete
when matched then
update set ACTIONCODE = 3;
update dbo.TA_CONSTITUENT
set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
where ID in(select INSERTED.CONSTITUENTID
from INSERTED
inner join dbo.CONSTITUENTNOTETYPECODE c on c.ID = INSERTED.CONSTITUENTNOTETYPECODEID
where c.DESCRIPTION = 'Constituent Comments');
if @@ROWCOUNT > 0
update dbo.TA_ACCOUNTS
set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
where ID in(select INSERTED.CONSTITUENTID
from INSERTED
inner join dbo.CONSTITUENTNOTETYPECODE c on c.ID = INSERTED.CONSTITUENTNOTETYPECODEID
where c.DESCRIPTION = 'Constituent Comments');
end
end
|
Trigger Definition
