I've looked through the mailing list and can't see
this issue raised there so...
There is a known issue with retrieving the id of the
last inserted row under MSSQL where IDENTITY cols are
used and there are triggers involved. It's pretty easy
to demonstrate. If I have this construction:
<db>
CREATE TABLE
test
(
id INT NOT NULL IDENTITY PRIMARY KEY,
code VARCHAR (10) NOT NULL UNIQUE
)
GO
CREATE TABLE
test_audit
(
id INT NOT NULL IDENTITY (100, 1) PRIMARY KEY,
test_id INT NOT NULL FOREIGN KEY REFERENCES test (id),
inserted_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by VARCHAR (60) NOT NULL DEFAULT SYSTEM_USER
)
GO
CREATE TRIGGER tr_test_i ON test FOR INSERT AS
INSERT INTO test_audit (test_id) SELECT id FROM inserted
GO
</db>
and insert something into test:
<db>
INSERT INTO test (code) VALUES ('ABC')
SELECT @@IDENTITY
SELECT * FROM test
</db>
The last id is 100 (the IDENTITY col from [test_audit])
while, as far as the user's concerned, [test] was the
only table affected. In sqlalchemy terms, this means
that the last_inserted_ids () could return misleading
values:
<python>
from sqlalchemy import *
db = BoundMetaData ("mssql://VODEV1/TimHolding")
test = Table ("test", db, autoload=True)
r = test.insert ().execute (code="DEF")
print r.last_inserted_ids ()
# => [101]
list (test.select ().execute (id=101))
# => []
</python>
What are the alternatives? Well, there are two:
IDENT_CURRENT ('tablename')
gives the last identity value assigned to this table
*in any session* (<alert>race condition</alert>)
or
SCOPE_IDENTITY ()
which seems to be what we're after here; it's like @@IDENTITY
but for the same "scope" (not a widely-used term in SQL
Server circles, as far as I know). The documentation
specifically gives this case as an example.
Looks to me like this would be the best bet for sqlalchemy's
purposes, but I'm sure there's a downside somewhere ;)
Comments?
TJG
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
