> from an 'identity' column called 'identifier'. Do I have to
> do another query just to get that or is there some short-hand
> to get info from the most recent insert. Since it's an
> identity column, I don't have the info handy until the record
> is made.
This is a perennial subject on this list, so you'll be able to find quite a
few answers in the list archives.
However, to summarize, you can select the newly generated identity value in
another query, or you can attach a trigger to the table that returns the
identity when a record is inserted, or you can write a stored procedure that
performs both the insert and the selection of the new identity, or you may
be able to perform both within a single SQL batch depending on your
platform.
If you're using SQL Server, for instance, you can use @@IDENTITY (or
SCOPE_IDENTITY in newer versions) to refer to the new identity column value.
If you do use two queries, you'll need to ensure that they're treated as a
single transaction in such a way that when one request inserts a record, no
other request can do so until the first request has fetched the new
identity. You can use the CFTRANSACTION tag for this, although you may have
to change the ISOLATION_LEVEL attribute to "SERIALIZABLE" to make this work.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
