TR_ADDRESS_TASYNC_I

          CREATE trigger TR_ADDRESS_TASYNC_I on dbo.ADDRESS after insert not for replication
          as begin

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

            if dbo.UFN_B2T_CONTEXTISSYNC() = 0
            begin
              insert into dbo.TA_ADDRESS (ID, SYNCID, ACTIONCODE)
              select ID, newid(), 1
              from INSERTED
              -- don't sync records for groups

              where not exists(select 'x'
                           from dbo.GROUPDATA d
                           where d.ID = INSERTED.CONSTITUENTID
                           and d.GROUPTYPECODE = 1);
              --

              -- 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

              --

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

              --

              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));
              --

              -- flag additional records for when primary is inserted and the old primary for the 

              -- accounts has to be unflagged.  This is to handle the case where the inserted record

              -- is the first one for the household and a member's primary address was synced as 

              -- the account's primary in TA.  This record now has to be unflagged as the account's

              -- primary.

              --

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

              -- When inserting a household address that matches a member's address, flag the member's

              -- row for update, in this case the member's row will be deleted in TA and will be associated

              -- with the household's address.

              --

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

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

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

              -- or the record being inserted 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)
              --

              -- flag constituents when inserting a master to couple to a household master.  This is to update

              -- the primary address IDs for the name record. 

              --

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

              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 in (1,3) then ACTIONCODE else 2 end
              where ID in(select CONSTITUENTID from INSERTED);
            end
          end