SCOPE_IDENTITY (Transact-SQL)
Devuelve el último valor de identidad insertado en una columna de identidad en el mismo ámbito. Un ámbito es un módulo: un procedimiento almacenado, desencadenador, función o lote. Por tanto, dos instrucciones están en el mismo ámbito si se encuentran en el mismo procedimiento almacenado, función o lote.
.gif "Icono de vínculo a temas")
Sintaxis
SCOPE_IDENTITY()
Tipos de valor devueltos
numeric(38,0)
Notas
SCOPE_IDENTITY, IDENT_CURRENT y @@IDENTITY son funciones parecidas ya que devuelven valores insertados en columnas de identidad.
IDENT_CURRENT no está limitado por el ámbito y la sesión; se limita a una tabla especificada. IDENT_CURRENT devuelve el valor generado para una tabla específica en cualquier sesión y cualquier ámbito. Para obtener más información, vea IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY y @@IDENTITY devuelven los últimos valores de identidad generados en una tabla en la sesión actual. No obstante, SCOPE_IDENTITY solo devuelve los valores insertados en el ámbito actual; @@IDENTITY no se limita a un ámbito específico.
Por ejemplo, suponga que hay dos tablas, T1 y T2, y un desencadenador INSERT definido en T1. Cuando se inserta una fila en T1, el desencadenador se activa e inserta una fila en T2. Este escenario muestra dos ámbitos: la inserción en T1 y la inserción en T2 como resultado del desencadenador.
Suponiendo que T1 y T2 tienen columnas de identidad, @@IDENTITY y SCOPE_IDENTITY devolverán distintos valores al finalizar una instrucción INSERT en T1. @@IDENTITY devolverá el último valor de la columna de identidad insertada en cualquier ámbito en la sesión actual. Este es el valor insertado en T2. SCOPE_IDENTITY() devolverá el valor IDENTITY insertado en T1. Es la última inserción que se ha producido en el mismo ámbito. La función SCOPE_IDENTITY() devolverá el valor NULL si se llama a la función antes de que se ejecuten las instrucciones INSERT en una columna de identidad en el ámbito.
Las instrucciones y transacciones con errores pueden cambiar la identidad actual de una tabla y crear huecos en los valores de columna de identidad. El valor de identidad jamás se revierte, aun cuando no se haya confirmado la transacción que intentó insertar el valor en la tabla. Por ejemplo, si se produce un error en una instrucción INSERT debido a una infracción de tipo IGNORE_DUP_KEY, el valor de identidad actual de la tabla se sigue incrementando.
Ejemplos
A. Usar @@IDENTITY y SCOPE_IDENTITY con desencadenadores
Este ejemplo crea dos tablas, TZ y TY, y un desencadenador INSERT en TZ. Cuando se inserta una fila en TZ, el desencadenador Ztrig se activa e inserta una fila en 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
Éste es el conjunto de resultados.
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. Usar @@IDENTITY y SCOPE_IDENTITY() con una replicación
Los ejemplos siguientes muestran cómo se usan @@IDENTITY y SCOPE_IDENTITY() para las inserciones en una base de datos publicada para la replicación de mezcla. Las dos tablas de los ejemplos se encuentran en la base de datos de ejemplo AdventureWorks: Person.ContactType no está publicado y Sales.Customer sí. La replicación de mezcla agrega desencadenadores a las tablas publicadas. Por lo tanto, @@IDENTITY puede devolver el valor de la inserción en una tabla de sistema de replicación en lugar de la inserción en una tabla de usuario.
La tabla Person.ContactType tiene un valor de identidad máximo de 20. Si inserta una fila en la tabla, @@IDENTITY y SCOPE_IDENTITY() devolverán el mismo valor.
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
Éste es el conjunto de resultados.
SCOPE_IDENTITY
21
@@IDENTITY
21
La tabla Sales.Customer tiene un valor de identidad máximo de 29483. Si inserta una fila en la tabla, @@IDENTITY y SCOPE_IDENTITY() devolverán valores diferentes. SCOPE_IDENTITY() devuelve el valor de la inserción en la tabla de usuario, mientras que @@IDENTITY devuelve el valor de la inserción en la tabla del sistema de replicación. Use SCOPE_IDENTITY() para las aplicaciones que necesitan obtener acceso al valor de identidad insertado.
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (8,'S')
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO
Éste es el conjunto de resultados.
SCOPE_IDENTITY
29484
@@IDENTITY
89