CREATE trigger TR_CONSTITUENCY_TASYNC_U on dbo.CONSTITUENCY after update not for replication
as begin
if dbo.UFN_B2T_CONTEXTISSYNC() = 0
begin
-- normally this is triggering an update of classifications
-- if constituencycode changed from one not mapped to classifications to one that is, this triggers an insert instead
merge dbo.TA_CONSTITUENCY t
using (select i.ID
from INSERTED i
-- only keep constituencies using codes mapped from classifications
inner join dbo.B2TROWS jclassification_code on jclassification_code.TATABLE = 'T2B_CLASSIFICATION_VALUE' and jclassification_code.BBECTABLE = 'CONSTITUENCYCODE' and jclassification_code.BBECID = i.CONSTITUENCYCODEID) 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 constituencycode changed to one not mapped to classifications, delete the classification from TA
merge dbo.TA_CONSTITUENCY t
using (select i.ID
from INSERTED i
where not exists(select 'x'
from dbo.B2TROWS jclassification_code
where jclassification_code.TATABLE = 'T2B_CLASSIFICATION_VALUE'
and jclassification_code.BBECTABLE = 'CONSTITUENCYCODE'
and jclassification_code.BBECID = i.CONSTITUENCYCODEID)) 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.B2TROWS r on r.TATABLE = 'ACCOUNT_TYPE' and r.BBECTABLE = 'CONSTITUENCYCODE' and r.BBECID = INSERTED.CONSTITUENCYCODEID);
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.B2TROWS r on r.TATABLE = 'ACCOUNT_TYPE' and r.BBECTABLE = 'CONSTITUENCYCODE' and r.BBECID = INSERTED.CONSTITUENCYCODEID);
end
end
|
Trigger Definition
