Alternative to "PDO::lastInsertId" / "mysql_insert_id"

Question

I always hear that using "lastInsertId" (or mysql_insert_id() if you're not using PDO) is evil. In case of triggers it obviously is, because it could return something that's totally not the last ID that your INSERT created.

$DB->exec("INSERT INTO example (column1) VALUES ('test')");
// Usually returns your newly created ID.
// However when a TRIGGER inserts into another table with auto-increment:
// -> Returns newly created ID of trigger's INSERT
$id = $DB->lastInsertId();

What's the alternative?

Answer 1

If you go the route of ADOdb (http://adodb.sourceforge.net/), then you can create the insert ID before hand and explicitly specific the ID when inserting. This can be implemented portably (ADOdb supports a ton of different databases...) and guarantees you're using the correct insert ID.

The PostgreSQL SERIAL data type is similar except that it's per-table/per-sequence, you specify the table/sequence you want the last insert ID for when you request it.