CREATE trigger TR_CONSTITUENTNOTE_TASYNC_I on dbo.CONSTITUENTNOTE after insert not for replication
as begin
if dbo.UFN_B2T_CONTEXTISSYNC() = 0
begin
insert into dbo.TA_CONSTITUENTNOTE (ID, SYNCID, ACTIONCODE)
select INSERTED.ID, newid(), 1
from INSERTED
-- only insert 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 = INSERTED.CONSTITUENTNOTETYPECODEID
-- 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
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
