Jira issue originally created by user michael.anthon:
The way that the PDOSqlsrv driver fetches the identity value for a freshly inserted record will fail if there are triggers on a table that do a secondary insert on a table that also has an identity column.
This is the case when you set up merge replication in SQL Server. The replication creates a series of triggers on the tables to catch any of the changes made for the purposes of replication and inserts those changes elsewhere.
We have switched to using the native SQLSrv drivers instead to work around this since that uses "SELECT SCOPE_IDENTITY() AS LastInsertId" to fetch the value during the insert command.
Comment created by @deeky666:
I'm not quite sure whether we can fix this is in a reasonable way. See PDO's lastInsertId method in itself is documented to be very inconsistent and behaves differently throughout different drivers and even database versions.
This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.
Because of this we do not have tests in our testsuite yet that cover the last insert id topic that work as expected on all drivers.
IIRC the fact that DBAL's implementation for the native sqlsrv driver uses "SELECT SCOPE_IDENTITY() AS LastInsertId" is a workaround to support this feature at all and come around the driver limitation IIRC. This as such is a dirty workaround and should IMO not be relied on for scenarios such as you describe.
I'm not quite sure what the expected behaviour of PDO drivers in general would be concerning triggers on PK columns that do other inserts regarding last insert IDs. IMO this is not a Doctrine bug but rather an unsupported use case or even a driver bug/limitation? Not sure on this.
The only thing we could actually do is implement the same workaround we have in the native sqlsrv driver for the PDO driver. But I would rather not do this for such an edge case scenarion.
But that's just my opinion :)
Comment created by michael.anthon:
Yes, I agree that any workaround will be a bit of a dirty hack.
The main problem is the PDO driver using @@identity to get the last inserted id, which is pretty much the wrong way to do it in all but the simplest of cases... SCOPEIDENTITY is there for a reason but won't work when called subsequently since it's run inside an spprepexec and will be out of scope anyway (it has to be tacked onto the end of the insert statement as it is in the sqlsrv driver)
There's another piece of code in the SQLSrvConnection that uses this method...
$sql = "SELECT IDENT_CURRENT(".$this->quote($name).") AS LastInsertId";
That could potentially be used as well and would probably give a more accurate answer but is also subject to race conditions on busy systems.
We think we ran into this issue; while searching for 'last insert id' strategies on sqlserver (2008 in our case), I found this SO post claiming IDENT_CURRENT will return any last inserted id on any scope AND session (http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row). In our case an additional trigger is executed upon insertion, resulting in returning the triggers inserted id, instead of the expected id from the table.
I think either SCOPE_IDENTITY() or "OUTPUT" should be used instead. The latter one would include altering the insert statement though.