-- main table we want to insert and get id back
CREATE TABLE TABLE_A(int identity not null primary key, name varchar(50));

-- create log table with a higher starting id
create table table_b(int identity(10000,1) not null primary key, username varchar(50), created_date datetime);

-- trigger code
ALTER TRIGGER SomeTriggerName ON TABLE_A
FOR INSERT, UPDATE, DELETE
AS
...
-- log the record just inserted
insert into table_b ... 
end

EntityManagerFactory emf = Persistence.createEntityManagerFactory("EclipseLinkPkTestPU");
EntityManager em = emf.createEntityManager();

TableA tableA = new TableA();
tableA.setName("Testing123");

em.getTransaction().begin();
em.persist(tableA);
em.flush();
em.getTransaction().commit();

System.out.println("PK: " + tableA.getId());

Long maxPk = (Long) em.createNativeQuery("select max(id) from table_a").getSingleResult();
System.out.println("Max PK: " + maxPk);

em.close();
emf.close();

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=SomeDatabase", "someUser", "somePass");
PreparedStatement stmnt = con.prepareStatement("INSERT INTO TABLE_A (name) VALUES (?); SELECT SCOPE_IDENTITY()"); //f this works with SCOPE_IDENTITY as well as @@IDENTITY

//f this obviously works as it's the generic JDBC way of getting the pk after an insert statement:
//f PreparedStatement stmnt = con.prepareStatement("INSERT INTO TABLE_A (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS);

stmnt.setString(1, "Just testing");
stmnt.executeUpdate();

long pk = -1L;
try (ResultSet rsKey = stmnt.getGeneratedKeys()) {
    if (rsKey != null && rsKey.next()) {
        pk = rsKey.getLong(1);
    } else {
        System.err.println("!! no key returned");
    }
}

System.out.println("GeneratedKeys PK: " + pk);

//f this is the default EclipseLink behaviour that calls @@IDENTITY
try (ResultSet rsSelectAtIdentity = con.createStatement().executeQuery("select @@IDENTITY")) {
    if (rsSelectAtIdentity.next()) {
        Long scopePk = (Long) rsSelectAtIdentity.getLong(1);
        System.out.println("@@IDENTITY PK: " + scopePk); //f this prints the incorrect pk
    } else {
        System.err.println("NO result for @@IDENTITY");
    }
}

//f this is the suggested fix, but doesn't work as it is called outside of the initial statement scope.
try (ResultSet rsSelectScopeIdentity = con.createStatement().executeQuery("select SCOPE_IDENTITY()")) {
    if (rsSelectScopeIdentity.next()) {
        Long scopePk = (Long) rsSelectScopeIdentity.getLong(1);
        System.out.println("Scope PK: " + scopePk); //f this prints 0, which is obviously incorrect
    } else {
        System.err.println("NO result for SCOPE_IDENTITY()");
    }
}

Long maxPk;
try (ResultSet rsSelectPk = con.createStatement().executeQuery("select max(id) from table_a")) {
    rsSelectPk.next();
    maxPk = (Long) rsSelectPk.getLong(1);
}
System.out.println("Max PK: " + maxPk); //f this is the pk we expect