TR_ADDRESS_TASYNC_D

          CREATE trigger TR_ADDRESS_TASYNC_D on dbo.ADDRESS after delete not for replication
          as begin
            if dbo.UFN_B2T_CONTEXTISSYNC() = 1
              delete from dbo.TA_ADDRESS
              where ID in(select ID from DELETED)
            else
            begin
              merge dbo.TA_ADDRESS t
              using (select ID from DELETED) d
              on (d.ID = t.ID)
              when matched and t.ACTIONCODE = 1 then
                delete
              when matched then
                update set ACTIONCODE = 3;
              --

              -- reset the primary.

              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_ADDRESS t, DELETED i
              where t.ID = dbo.UFN_B2T_GETACCOUNTPREFERREDADDRESS(i.CONSTITUENTID,0)
              and i.ISPRIMARY = 1
              --

              -- When deleting a master row (t2), flag the linked non-master (t) rows for insert

              -- into TA.

              --

              update t
              set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_ADDRESS t
              inner join dbo.TA_ADDRESS t2 on t.MASTERSYNCID = t2.SYNCID
              inner join DELETED d on d.ID = t2.ID
              --

              -- flag the constituent record that the address is on or

              -- flag the members in the household for update when a primary record is deleted

              -- and the constituent does not have an address

              --

              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 DELETED 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 DELETED 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.ADDRESS 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 DELETED 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.ADDRESS a where t.ID = a.CONSTITUENTID)
              --

              -- flag members when household master records are deleted and the member

              -- record is primary.

              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_CONSTITUENT t, dbo.TA_ADDRESS d, dbo.TA_ADDRESS m, dbo.ADDRESS a, DELETED i 
              where t.ID in (select MEMBERID from dbo.UFN_B2T_GET_HOUSEHOLDANDMEMBERS(i.CONSTITUENTID, 1))
              and i.ID = d.ID
              and d.SYNCID = m.MASTERSYNCID
              and m.ID = a.ID
              and t.ID = a.CONSTITUENTID
              and a.ISPRIMARY = 1
              --

              -- do the same for org contacts

              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_CONSTITUENT t, dbo.TA_ADDRESS d, dbo.TA_ADDRESS m, dbo.ADDRESS a, DELETED i 
           where dbo.UFN_CONSTITUENT_ISORGANIZATION (i.CONSTITUENTID) = 1
              and t.ID in (select r.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP r where r.RELATIONSHIPCONSTITUENTID = i.CONSTITUENTID)
              and i.ID = d.ID
              and d.SYNCID = m.MASTERSYNCID
              and m.ID = a.ID
              and t.ID = a.CONSTITUENTID
              and a.ISPRIMARY = 1
              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_NAMES0 t
              inner join DELETED i on i.CONSTITUENTID = t.ID and i.ISPRIMARY = 1

              -- Refresh accounts.address_sts

              update dbo.TA_ACCOUNTS
              set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
              where ID in(select CONSTITUENTID from DELETED);
            end
          end