In a table I would like to create a unique code, and to ensure the code is unique and protected against concurrency issues I implemented a database trigger. However when there are a number of inserts around the same time (meaning single row inserts), the code that is created is not unique. Is my expectation wrong or my code insufficient?

CREATE OR ALTER TRIGGER AfterProjectInsert
ON [Projects]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        UPDATE v
        SET 
            v.[Sequence] = seq.[NewSequence],
            v.[ProjectCode] = CONCAT(i.[Organization], '-', seq.[NewSequence])
        FROM [Projects] v
        JOIN inserted i ON v.[Id] = i.[Id]
        CROSS APPLY (
            SELECT COALESCE(MAX(v2.[Sequence]), 0) + 1 AS [NewSequence]
            FROM [Projects] v2
            WHERE v2.[Organization] = i.[Organization]
        ) seq

        -- Ensure ProjectCode is unique
        IF EXISTS (
            SELECT 1
            FROM [Projects] v
            JOIN inserted i ON v.[Id] = i.[Id]
            GROUP BY v.[ProjectCode]
            HAVING COUNT(*) > 1
        )
        BEGIN
            RAISERROR ('Duplicate ProjectCode detected', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END
    END
END;