如何在Sql Server中準(zhǔn)確的獲得標(biāo)識值

SQL Server有三種不同的函數(shù)可以用來獲得含有標(biāo)識列的表里最后生成的標(biāo)識值：

IT學(xué)習(xí)網(wǎng)，全國最大的IT在線學(xué)習(xí)網(wǎng)站！

　　以上三個函數(shù)雖然都可以返回數(shù)據(jù)庫引擎最后生成插入標(biāo)識列的值，但是根據(jù)插入行的來源(例如：存儲過程或觸發(fā)器)以及插入該行的連接不同，這三個函數(shù)在功能上也有所不同。 內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　@@IDENTITY函數(shù)可以返回所有范圍內(nèi)當(dāng)前連接插入最后所生成的標(biāo)識值(包括任何調(diào)用的存儲過程和觸發(fā)器)。這個函數(shù)不止可以適用于表。函數(shù)返回的值是最后表插入行生成的標(biāo)識值。

copyright www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　SCOPE_IDENTITY()函數(shù)跟上一個函數(shù)幾乎是一摸一樣的，不同的地方：即前者返回的值只限于當(dāng)前范圍(即執(zhí)行中的存儲過程)。 IT學(xué)習(xí)網(wǎng)，全國最大的IT在線學(xué)習(xí)網(wǎng)站！

　　最后是IDENT_CURRENT函數(shù)，它可以用于所有范圍和所有連接，獲得最后生成的表標(biāo)識值。跟前面兩個函數(shù)不同的是，這個函數(shù)只用于表，并且使用[數(shù)據(jù)表名]作為一個參數(shù)。 www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　我們可以舉實例來演示上述函數(shù)是如何運作的。

www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　首先，我們創(chuàng)建兩個簡單的例表：一個代表客戶表，一個代表審計表。創(chuàng)建審計表的目的是為了跟蹤數(shù)據(jù)庫里插入和刪除信息的所有記錄。 內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　以下是引用片段： copyright www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　CREATE TABLE dbo.customer
　　(customerid INT IDENTITY(1,1) PRIMARY KEY)
　　GO
　　CREATE TABLE dbo.auditlog
　　(auditlogid INT IDENTITY(1,1) PRIMARY KEY,
　　customerid INT, action CHAR(1),
　　changedate datetime DEFAULT GETDATE())
　　GO

　　然后，我們還要創(chuàng)建一個存儲過程和一個輔助觸發(fā)器，這個存儲過程將在數(shù)據(jù)庫表里插入新的客戶行，并返回生成的標(biāo)識值，而觸發(fā)器則會向?qū)徲嫳聿迦胄校?內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　以下是引用片段：

本文來自IT學(xué)習(xí)網(wǎng)本文來自www.it521.com.cn

　　CREATE PROCEDURE dbo.p_InsertCustomer @customerid INT output
　　AS
　　SET nocount ON
　　INSERT INTO dbo.customer DEFAULT VALUES
　　SELECT @customerid = @@identity
　　GO
　　CREATE TRIGGER dbo.tr_customer_log ON dbo.customer
　　FOR INSERT, DELETE
　　AS
　　IF EXISTS (SELECT 'x' FROM inserted)
　　INSERT INTO dbo.auditlog (customerid, action)
　　SELECT customerid, 'I'
　　FROM inserted
　　ELSE
　　IF EXISTS (SELECT 'x' FROM deleted)
　　INSERT INTO dbo.auditlog (customerid, action)
　　SELECT customerid, 'D'
　　FROM deleted
　　GO www.it521.com.cnIT學(xué)習(xí)網(wǎng)

現(xiàn)在我們可以執(zhí)行程序，創(chuàng)建客戶表的第一行了： IT學(xué)習(xí)網(wǎng)，全國最大的IT在線學(xué)習(xí)網(wǎng)站！

　　以下是引用片段： www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

IT學(xué)習(xí)網(wǎng)，全國最大的IT在線學(xué)習(xí)網(wǎng)站！

　　執(zhí)行后返回了我們需要的第一個客戶的值，并記錄了插入審計表的條目。到目前為止，數(shù)據(jù)顯示沒有任何問題。

　　假設(shè)由于先前溝通出現(xiàn)了偏差，一個客戶服務(wù)代表現(xiàn)在需要從數(shù)據(jù)庫里刪除掉這個新增的客戶。我們現(xiàn)在就來把新插入的客戶行刪除掉： 內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　以下是引用片段：

www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　DELETE FROM dbo.customer WHERE customerid = 1

　　現(xiàn)在，客戶工作表為空表，而審計工作表里則有兩行——第一行是記錄第一次插入行，第二行是記錄刪除客戶記錄。 IT學(xué)習(xí)網(wǎng)，全國最大的IT在線學(xué)習(xí)網(wǎng)站！

　　現(xiàn)在我們再往數(shù)據(jù)庫里增加第二個客戶信息并檢測一下獲得的標(biāo)識值：

　　以下是引用片段：

內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

　　哇!看看出現(xiàn)了什么情況!如果我們現(xiàn)在再看客戶工作表，就會發(fā)現(xiàn)雖然創(chuàng)建了客戶2，但是我們的程序返回的標(biāo)識值為3!到底出了什么問題呢?回想一下，前面講過@@IDENTITY函數(shù)的作用范圍，它會返回主程序調(diào)用的任何存儲過程或觸動任何觸發(fā)器最后生成的標(biāo)識值，取決于哪一個在函數(shù)被調(diào)用前最后生成標(biāo)識值。在我們的例子里，初始范圍是p_InsertCustomer，然后是觸發(fā)器用來記錄插入條目的tr_customer_log。因此我們返回獲得的標(biāo)識值是審計工作表里觸發(fā)器插入生成的標(biāo)識值，而不是我們想要的客戶工作表里的生成的標(biāo)識值。 copyright www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　在SQL Server 2000之前的版本，@@IDENTITY函數(shù)是獲得標(biāo)識值的唯一方法。由于會出現(xiàn)這樣的存儲過程/觸發(fā)器問題，SQL Server開發(fā)團(tuán)隊在SQL Server 2000中引入了 SCOPE_IDENTITY()和IDENT_CURRENT這兩個函數(shù)來解決這個問題。所以在舊的SQL Server版本里，要解決這個問題比較麻煩。如果是SQL Server6.5版本，我建議可以去掉標(biāo)識列，然后創(chuàng)建一個可以包含下一個需要使用的值的輔助表，可以達(dá)到標(biāo)識列的作用效果。不過這個辦法也不是什么高明的辦法。

內(nèi)容來自IT學(xué)習(xí)網(wǎng)www.it521.com.cn

　　現(xiàn)在我們來修改一下存儲過程來使用SCOPE_IDENTITY()函數(shù)，并重新執(zhí)行程序來添加第三個客戶條目：
以下是引用片段： copyright www.it521.com.cnIT學(xué)習(xí)網(wǎng)

　　ALTER PROCEDURE dbo.p_InsertCustomer @customerid INT output
　　AS
　　SET nocount ON
　　INSERT INTO dbo.customer DEFAULT VALUES
　　SELECT @customerid = SCOPE_IDENTITY()
　　GO
　　DECLARE @customerid INT
　　EXEC dbo.p_InsertCustomer @customerid output
　　SELECT @customerid AS customerid

　　我們返回的標(biāo)識值還是3，不過這次我們獲得的標(biāo)識值是正確的，因為我們添加了第三個客戶條目。如果我們檢查一下審計工作表，就會發(fā)現(xiàn)里面已經(jīng)有第四個條目記錄新插入的客戶記錄。由于函數(shù)SCOPE_IDENTITY()只作用于當(dāng)前范圍，只返回當(dāng)前執(zhí)行程序的值，這樣就避免了發(fā)生剛才那樣的問題。 copyright www.it521.com.cnIT學(xué)習(xí)網(wǎng)