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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---