OK, I replied to the other thread already, which is really the same issue.
See my response there about backward-compatibility.
At any rate, we could make it a connection variable like
auto_identity_insert. Patches welcome.
Rick
On 3/7/07, Tim Golden <[EMAIL PROTECTED]> wrote:
>
>
> 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
-~----------~----~----~----~------~----~------~--~---
