>> SELECT IDENT_CURRENT('tablename ') which returns the
>> latest identity in the table name in question. That
>> also prevents getting the identity of the wrong table
>> in the event of the query being used as part of a
>> trigger which affects multiple tables.
Jacob Munson wrote:
> Unless someone has created a new record after your insert,
> then you'll get their identity instead. SCOPE_IDENTITY,
> on the other hand, returns the last identity value
> generated for any table in the current session and the
> current scope. So with one, you might get someone
> else's identity, and with the other you might get the
> wrong table.
> Can we ever win? :)
Nope that's not true. scope_identity() works on the last table
specified in the current batch. You can test this by creating a table
with an identity column and a trigger which inserts data into a 2nd
table also with an identity column. Make sure there is a discrepancy
between the two tables by padding the 2nd table with some data, so
that the trigger will insert a different value. When you then insert a
value into the table on which the trigger was created and return the
value of scope_identity() within the same statement batch, you'll get
the value of the table you inserted into (while the triggered insert
goes merilly on past you).
However, thank you for the reminder that the ident_current() function
is not thread-safe, and as such would require a serializable
cftransaction (or equivalent) to make it thread-safe, which is not
true of scope_identity(). But with the serializable transaction (CF or
DB initiated) it could work reliably as I understand it, it just
wouldn't be the most efficient method.
s. isaac dealey 434.293.6201
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236971
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
