SCOPE_IDENTITY (Transact-SQL)

Renvoie la dernière valeur d'identité insérée dans une colonne d'identité dans la même étendue. Une étendue est un module : procédure stockée, déclencheur, fonction ou lot. Par conséquent, deux instructions sont dans la même étendue si elles se trouvent dans la même procédure stockée ou fonction, ou dans le même traitement.

Conventions de syntaxe Transact-SQL

Syntaxe

SCOPE_IDENTITY()

Type des valeurs renvoyées

numeric(38,0)

Notes

SCOPE_IDENTITY, IDENT_CURRENT et @@IDENTITY sont des fonctions similaires car elles renvoient des valeurs insérées dans des colonnes d'identité.

IDENT_CURRENT n'est pas limitée par l'étendue et par la session ; elle est limitée à une table spécifiée. IDENT_CURRENT renvoie la valeur générée pour une table spécifique dans n'importe quelle session et n'importe quelle étendue. Pour plus d'informations, consultez IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY et @@IDENTITY renvoient les dernières valeurs d'identité générées dans une table de la session active. Toutefois, SCOPE_IDENTITY renvoie les valeurs insérées uniquement dans l'étendue actuelle. @@IDENTITY n'est pas limitée à une étendue spécifique.

Par exemple, deux tables T1 et T2 et un déclencheur INSERT sont définis sur T1. Lorsqu'une ligne est insérée dans T1, le déclencheur est activé et insère une ligne dans T2. Ce scénario met en œuvre deux étendues : l'insertion dans T1 et l'insertion dans T2 par le déclencheur.

Supposons que T1 et T2 comportent les colonnes d'identité, @@IDENTITY et SCOPE_IDENTITY renvoient des valeurs différentes à la fin d'une instruction INSERT dans T1. @@IDENTITY renvoie la dernière valeur de la colonne d'identité insérée dans toutes les étendues au cours de la session active. Il s'agit de la valeur insérée dans T2. SCOPE_IDENTITY() renvoie la valeur IDENTITY insérée dans T1. Il s'agit de la dernière insertion qui s'est produite dans la même étendue. La fonction SCOPE_IDENTITY() renvoie la valeur NULL si la fonction est appelée avant qu'une instruction INSERT dans une colonne d'identité soit exécutée dans l'étendue.

Les instructions et les transactions en échec peuvent modifier l'identité actuelle d'une table et créer des trous dans les valeurs des colonnes d'identité. La valeur d'identité n'est jamais annulée, même si la transaction qui a essayé d'insérer la valeur dans la table n'est pas validée. Par exemple, si une instruction INSERT échoue à cause d'une violation d'identité IGNORE_DUP_KEY, la valeur d'identité actuelle de la table augmente quand même d'une unité.

Exemple

A. Utilisation des fonctions @@IDENTITY et SCOPE_IDENTITY avec des déclencheurs

L'exemple suivant crée deux tables, TZ et TY, ainsi qu'un déclencheur INSERT sur TZ. Lorsqu'une ligne est insérée dans la table TZ, le déclencheur (Ztrig) est activé et insère une ligne dans TY.

USE tempdb
GO
CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks.
Z_id   Z_name
-------------
1      Lisa
2      Mike
3      Carla

CREATE TABLE TY (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:
Y_id  Y_name
---------------
100   boathouse
105   rocks
110   elevator

/*Create the trigger that inserts a row in table TY 
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS 
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and determine what identity values you obtain 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

Voici l'ensemble des résultats.

SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope. This was the insert on table TZ.*/

@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger. This fired because of an earlier insert on TZ.*/

B. Utilisation des fonctions @@IDENTITY et SCOPE_IDENTITY() avec des déclencheurs

Les exemples suivants indiquent comment utiliser @@IDENTITY et SCOPE_IDENTITY() pour des insertions dans une base de données qui est publiée pour la réplication de fusion. Les deux tables mentionnées à titre d'exemple appartiennent à la base de données exemple AdventureWorks : Person.ContactType n'est pas publiée et Sales.Customer est publiée. La réplication de fusion ajoute des déclencheurs aux tables qui sont publiées. Par conséquent, @@IDENTITY peut retourner la valeur de l'insertion dans une table système de réplication au lieu de l'insertion dans une table utilisateur.

La table Person.ContactType possède une valeur d'identité maximale de 20. Si vous insérez une ligne dans la table, @@IDENTITY et SCOPE_IDENTITY() retournent la même valeur.

USE AdventureWorks;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager')
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

Voici l'ensemble des résultats.

SCOPE_IDENTITY
21

@@IDENTITY
21

La table Sales.Customer possède une valeur d'identité maximale de 29483. Si vous insérez une ligne dans la table, @@IDENTITY et SCOPE_IDENTITY() retournent des valeurs différentes. SCOPE_IDENTITY() retourne la valeur de l'insertion dans la table utilisateur, alors que @@IDENTITY retourne la valeur de l'insertion dans la table système de réplication. Utilisez SCOPE_IDENTITY() pour les applications qui doivent accéder à la valeur d'identité insérée.

INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (8,'S')
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

Voici l'ensemble des résultats.

SCOPE_IDENTITY
29484

@@IDENTITY
89

Voir aussi

Référence

@@IDENTITY (Transact-SQL)