TR_ADDRESS_TASYNC_U

          CREATE trigger TR_ADDRESS_TASYNC_U on dbo.ADDRESS after update not for replication
          as begin

            declare @CHILDRECORDS table (ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, ISPRIMARY bit);
            declare @MASTERSYNCRECORDS table (ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier);

            if dbo.UFN_B2T_CONTEXTISSYNC() = 0
            begin
              -- flag this row

              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 INSERTED i on i.ID = t.ID
              --

              -- find and store all the addresses that looks the same as the record being updated.

              --

              insert into @CHILDRECORDS (ADDRESSID, 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.ADDRESS a on a.CONSTITUENTID = m.MEMBERID
              and a.COUNTRYID = i.COUNTRYID 
              and a.ADDRESSBLOCK = i.ADDRESSBLOCK 
              and a.CITY = i.CITY 
              and (a.STATEID = i.STATEID or (a.STATEID is null and i.STATEID is null)) 
              and (a.ADDRESSTYPECODEID = i.ADDRESSTYPECODEID or (a.ADDRESSTYPECODEID is null and i.ADDRESSTYPECODEID is null))
              and a.POSTCODE = i.POSTCODE 
              and ( (a.HISTORICALSTARTDATE = i.HISTORICALSTARTDATE) or (a.HISTORICALSTARTDATE is null and i.HISTORICALSTARTDATE is null) ) 
              and ( (a.HISTORICALENDDATE = i.HISTORICALENDDATE) or (a.HISTORICALENDDATE is null and i.HISTORICALENDDATE 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.ADDRESS a2 on a2.RELATIONSHIPID=r.ID
              where r.ISCONTACT=1 and
              i.COUNTRYID=a2.COUNTRYID and
              (i.STATEID=a2.STATEID or (i.STATEID is null and a2.STATEID is null)) and
              (i.ADDRESSBLOCK=a2.ADDRESSBLOCK or (a2.ADDRESSBLOCK is null and a2.ADDRESSBLOCK is null)) and
              (i.CITY=a2.CITY or (i.CITY is null and a2.CITY is null)) and
              (i.POSTCODE=a2.POSTCODE or (i.POSTCODE is null and a2.POSTCODE is null)) and
              ((a2.HISTORICALSTARTDATE = i.HISTORICALSTARTDATE) or (a2.HISTORICALSTARTDATE is null and i.HISTORICALSTARTDATE is null)) and
              ((a2.HISTORICALENDDATE = i.HISTORICALENDDATE) or (a2.HISTORICALENDDATE is null and i.HISTORICALENDDATE is null));
              --

              -- find all the records that is a child of the updated record (if any)

              --

              insert into @MASTERSYNCRECORDS (ADDRESSID, CONSTITUENTID)
              select a.ID, a.CONSTITUENTID
              from INSERTED i
              inner join dbo.TA_ADDRESS mta on i.ID = mta.ID
              inner join dbo.TA_ADDRESS ta on mta.SYNCID = ta.MASTERSYNCID
              inner join dbo.ADDRESS a on ta.ID = a.ID
              --

              --When updating a master record, flag related rows for update

              --which may result in the related rows being inserted as new rows in TA

              --(decoupling).  Only do this if the updates resulted in unlinking the 

              --linked rows.

              --

              update t
              set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from dbo.TA_ADDRESS t
              where t.ID not in (select ADDRESSID from @CHILDRECORDS) and t.ID in (select ADDRESSID from @MASTERSYNCRECORDS)
              --

              -- update a household record to match an individual record.  Flag

              -- the matched rows for update.

              -- (coupling)

              --

              update dbo.TA_ADDRESS
              set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
              where ID in (select ADDRESSID from @CHILDRECORDS)
              and MASTERSYNCID is null
              --

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

              -- and the constituent does not have a record or if the record matches the updated record

              -- or the record being updated 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.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 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.ADDRESS a where t.ID = a.CONSTITUENTID)
              --

              -- when updating a primary record and it matches an existing primary address in the household

              -- flag that constituent. (coupling)

              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from  dbo.TA_CONSTITUENT t
              where t.ID in (select constituentid from @CHILDRECORDS where ISPRIMARY = 1)

              --

              --when updating a primary record and it unlinks the record from the master, flag that 

              --constituent (decoupling)

              --

              update t
              set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
              from  dbo.TA_CONSTITUENT t
              where t.ID not in (select CONSTITUENTID from @CHILDRECORDS) and t.ID in (select CONSTITUENTID from @MASTERSYNCRECORDS)
              --

              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=1 then 1 else 2 end
              where ID in(select CONSTITUENTID from INSERTED union all select CONSTITUENTID from DELETED);
            end
          end