SQL Server 2008 R2: After Insert Trigger and Query returning unexpected results for a split second

I am having trouble with an After Insert trigger and a query running right afterwards.

The query goes and finds the most current record in each table by using OUTER APPLY with a table-valued function and should return one record. However, my application that updates the table and returns the new results runs in an error because for a split second the table valued function returns 2 records instead of 1.

I realize I could use SELECT TOP 1 in the function, but I do not want to do that because there should only ever be 1 current record or else the data is corrupt. I do not want to use an INSTEAD OF INSERT trigger either because I would rather not write the inserts for every table in the trigger.

I thought by doing it this was my insert would lock the table in a transaction and the query would have to wait. I included my query below and my setup

-- Table I am inserting to. The business key is Location and ID but we need to keep track of date ranges as well, this is very important
CREATE TABLE Foo(
    Location char(2) NOT NULL,
    Id varchar(25) NOT NULL,
    Effective datetimeoffset(0) NOT NULL,
    Expired datetimeoffset(0) NOT NULL,
    CreatedBy varchar(20) NOT NULL,
    BarType varchar(15) NULL,
    BarSize decimal(5, 3) NULL,
 CONSTRAINT PK_Foo_Effective_Id_Expired_Location PRIMARY KEY CLUSTERED 
(
    Effective ASC,
    Id ASC,
    Expired ASC,
    Location ASC
)
)

ALTER TABLE Foo ADD  CONSTRAINT DF_Foo_Effective  DEFAULT (sysdatetimeoffset()) FOR Effective
ALTER TABLE Foo ADD  CONSTRAINT DF_Foo_Expired  DEFAULT (CONVERT(datetimeoffset(0),'12/31/9999 23:59:59.000',0)) FOR Expired
ALTER TABLE Foo ADD  CONSTRAINT DF_Foo_CreatedBy  DEFAULT ('SYS') FOR CreatedBy
ALTER TABLE Foo  WITH CHECK ADD  CONSTRAINT CK_Foo_ExpiredGreaterThanEffective CHECK  ((Expired>=Effective));
GO

-- Table type is used as a param to make sure date ranges do not overlap
CREATE TYPE FooPeriod AS TABLE 
(
  Location char(2),
  Id varchar(25),
  Effective datetimeoffset(0),
  Expired datetimeoffset(0)
)
GO

CREATE TRIGGER aiExpireCurrentFoo
   ON  Foo 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON

    -- Fill the custom table type with data from both tables
    DECLARE @foo FooPeriod
    DECLARE @inserted FooPeriod

    -- Get all records for ID and location while excluding the one that was just inserted
    INSERT INTO @foo(Id, Location, Effective, Expired)
    SELECT r.Id, r.Location, r.Effective, r.Expired 
    FROM 
        RC.Foo AS r
        INNER JOIN inserted AS i
            ON r.Id = i.Id
            AND r.Location = i.Location
    WHERE
        r.Effective <> i.Effective -- exclude the record just inserted
        OR r.Expired <> i.Expired

    INSERT INTO @inserted(Id, Location, Effective, Expired)
    SELECT Id, Location, Effective, Expired FROM inserted

    -- This function will check if any date range overlaps in the insert
    IF (dbo.fCheckOverlapDateRange(@foo, @inserted) = 1)
    BEGIN

        RAISERROR('You are attempting to insert a record between a date range that already exists for a id and location. Please check your values and retry.', 16, 1)
        ROLLBACK

    END
    ELSE
    BEGIN

        -- if everything is good update the expiration date of the current record that is not the newly inserted record
        UPDATE r
        SET Expired = SYSDATETIMEOFFSET()
        FROM
            inserted AS i
            INNER JOIN Foo AS r
                ON i.Effective <> r.Effective
                AND i.Id = r.Id
                AND r.Expired = '9999-12-31 23:59:59 +00:00'
                AND i.Location = r.Location

        DECLARE @RowCount INT = @@ROWCOUNT

    -- only one record should update
        IF (@RowCount != 0 AND @@ROWCOUNT <> (SELECT COUNT(*) FROM inserted))
        BEGIN

            RAISERROR('There is more than one current record with a datetime of 9999-12-31 23:59:59 +00:00. Please check your data for corruption', 16, 1)
            ROLLBACK

        END
    END
END

GO

-- returns the record at the given effective date (also used to get the most current record in my query)
CREATE FUNCTION fGetFoo
(
    @Location CHAR(2)
    ,@Id VARCHAR(25)
    ,@Effective DATETIMEOFFSET(0)
)

RETURNS TABLE
AS
RETURN
    SELECT
        BarType
        ,BarSize
  FROM 
    Foo
    WHERE
        Id = @Id
        AND Location = @Location
        AND Effective <= @Effective
        AND Expired >= @Effective;


 -- query that is returning 2 records for Foo for a split second
 SELECT * --I wouldn't use * , but just as an example
    FROM 
        SomOtherTable AS h
        OUTER APPLY RC.fGetSomeOtherFoo(h.Location, h.Id, SYSDATETIMEOFFSET()) AS m
        OUTER APPLY RC.fGetSomeOtherFoo(h.Location, h.Id, SYSDATETIMEOFFSET()) AS p
        OUTER APPLY RC.fGetSomeOtherFoo(h.Location, h.Id, SYSDATETIMEOFFSET()) AS md
        OUTER APPLY RC.fGetSomeOtherFoo(h.Location, h.Id, SYSDATETIMEOFFSET()) AS f
        OUTER APPLY RC.fGetFoo(h.Location, h.Id, SYSDATETIMEOFFSET()) AS bf
    WHERE h.Expired = '9999-12-31 23:59:59 +00:00'

UPDATE

I think I did not have a good understanding of the flow of operations for an AFTER INSERT trigger. According to this post,

AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfull Answer on MSDN

which leads me to believe that is why I am seeing the results of the insert before the update happens in my trigger