全面解决--如何获得刚插入数据库的记录的ID号?

如何获得刚插入数据库的记录的ID号?

1.SQL Server
对于SQL Server 2000来说，它提供了两个全新的函数(IDENT_CURRENT,SCOPE_IDENTITY),并且改进了@@IDENTITY的不足.当你插入新记录后，可以调用函数:
PRINT IDENT_CURRENT('table') '这将获得新的IDENTITY值，不管数据库中是不是有记录添加(这就避免了@@IDENTITY的连接限制)
或者：PRINT SCOPE_IDENTITY() '这将获得在当前存储过程，触发器等其他程序创建的最新记录的IDENTITY值.
而全局变量@@IDENTITY有一个问题，当对一张表执行insert时，如果该表有触发器程序在执行插入操作，然后，接着在另一张表中插入记录，这样返回@@IDENTITY值就是第二张表的IDENTITY值。
如果你用的不是SQL Server 2000，你最好一个简单的存储过程来解决这个问题。
CREATE PROCEDURE myProc
@param1 INT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO someTable
(
intField
)
VALUES
(
@param1
)
SET NOCOUNT OFF
SELECT NEWID = @@IDENTITY
END
在ASP中你可以这样做:
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
set rs = conn.execute("exec myProc @param1=" & fakeValue)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

2.Access
对于Access,你可以用下面这样的方法：
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
conn.execute "Insert into someTable(intField) values(" & fakeValue & ")"
set rs = conn.execute("select MAX(ID) from someTable")
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>
然而对于多人同时向数据库中添加数据，我们就要利用记录集的adOpenKeyset游标来防止出错。例如下面的例子:
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open "select [intField] from someTable where 1=0", conn, 1, 3
rs.AddNew
rs("intField") = fakeValue
rs.update
response.write "New ID was " & rs("id")

-----------------------------------------------------------

USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT 
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT   * FROM t6
--id is empty.

SELECT   * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY      
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()   
/* Returns the value 1, which was inserted by the 
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action 
so far in this session.*/

SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action 
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/

rs.close: set rs = nothing
conn.close: set conn = nothing
%>