If the trigger insets other rows on the same connection, that can foul
things up for @@identity, as the value is connection-global.
There is an alternative (can't remember the name now, something like
@@scope_identity?) that can be used in its stead that MIGHT fix this, but
using it will break backward compatibility to the point where MS added the
new variable (I think in MSSQL-2000).
Is anybody still using MSSQL-V7 or earlier with SA?
Rick
On 3/7/07, Tim Golden <[EMAIL PROTECTED]> wrote:
>
>
> polaar wrote:
> > I recently tried out sqlalchemy with mssql via pyodbc (after being
> > bitten by the adodbapi bug with the truncated parameters), and noticed
> > the following problem:
> >
> > On inserting records into tables with triggers, pyodbc fails on the
> > 'select @@identity as lastrowid' statement with an 'invalid cursor
> > state' error.
>
> OK, I can't reproduce this (and there's a follow-on issue which
> I'll pick up later). Just to clarify, I have this structure
> compiled on the database:
>
> <db>
> IF OBJECT_ID ('test_audit') IS NOT NULL
> DROP TABLE test_audit
> GO
> IF OBJECT_ID ('test') IS NOT NULL
> DROP TABLE test
> GO
>
> CREATE TABLE
> test
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> code VARCHAR (10) NOT NULL UNIQUE
> )
> GO
>
> CREATE TABLE
> test_audit
> (
> 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>
>
> That's a main table (test) an audit table (test_audit)
> into which test-INSERTs are triggered. Now, in sqlalchemy:
>
> <code>
> from sqlalchemy import *
> metadata = BoundMetaData ("mssql://VODEV1/TimHolding")
> test = Table ("test", metadata, autoload=True)
> result = test.insert ().execute (code = "ABC")
> print result.last_inserted_ids ()
> # => [1]
> </code>
>
> which is what I expected. If I explicitly set NOCOUNT OFF
> for my session (in case it's on by default) using:
>
> metadata.engine.raw_connection ().execute ("SET NOCOUNT OFF")
>
> then it still works.
>
> Is my case the situation you're describing? Or have I
> misunderstood somthing?
>
> 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
-~----------~----~----~----~------~----~------~--~---
