Jak zjistit hodnotu IDENTITY po insertu
Při návrhu tabulek se velmi často používá institut umělého klíče - identifikátoru záznamu (ID), který je uměle generován, obvykle rostoucí řadou. Pro generování těchto ID je často využívána funkce IDENTITY. Potíží je, že po vložení záznamu je často nutno hned vědět, jaké ID bylo vygenerováno, aby se jeho hodnota dala použít v dalších operacích. Pro zjištění vygenerované hodnoty ID nám SQL Server poskytuje dvojici funkcí - @@IDENTITY a SCOPE_IDENTITY(). Mezi těmito funkcemi je dost podstatný rozdíl, který se pokusíme odhalit na následujícím příkladu.
Příklad je nutno nejprve připravit. Pro jeho přípravu jsou použity již klasické tabulky Clovek (parent) a Pes (Child), které už sehrály svou roli v povídání o JOINech. Tady je skript pro jejich vytvoření:
CREATE
TABLE Clovek
(
Id int IDENTITY(1,1) NOT NULL,
Jmeno
varchar(30) NOT NULL,
CONSTRAINT pk_Clovek PRIMARY KEY (Id)
)
CREATE
TABLE Pes
(
Id
int IDENTITY(1,1) NOT NULL,
ClovekId
int NULL,
Jmeno
varchar(30) NOT NULL,
CONSTRAINT pk_Pes PRIMARY KEY (Id),
CONSTRAINT fk_Pes_Clovek FOREIGN KEY(ClovekId) REFERENCES Clovek (Id)
)
go
Následuje skript pro vytvoření triggeru nad tabulkou Clovek. Tento trigger provede při vložení záznamu do tabulky Clovek ještě vložení záznamu do tabulky Pes.
-- trigger, ktery se spusti pri vlozeni noveho cloveka
create
trigger ti_clovek_identity on Clovek
for
insert
as
insert
Pes (ClovekId, Jmeno)
select
inserted.Id, 'Pes_' + inserted.jmeno from inserted
go
Nakonec je třeba ještě vyrobit pár záznamů. Následující skript udělá to, že pro jednoho nově vloženého člověka vygeneruje deset psů. Jediným cílem tohoto skriptu je vyrobit nestejné hodnoty ID v tabulkách Clovek a Pes.
-- testovaci data - pro jedno ID v tabulce Clovek vygeneruje 10 ID v tabulce Pes
declare
@id int, @i int = 1
insert
Clovek values ('Test1')
select
@id = SCOPE_IDENTITY()
while
@i <= 10
begin
insert Pes(ClovekId, Jmeno)
values (@id, 'Pes_Test1_' + cast(@i as varchar))
set @i += 1
end
go
A teď konečně provedeme test! Nejprve se pojďme podívat, jaká ID budou v jednotlivých tabulkách a poznačme si jejich maximální hodnoty (pokud byly tabulky Clovek a Pes vyrobeny "načisto", a při spouštění skriptů nenastala žádná chyba, měly by být jejich hodnoty v tabulce Clovek 1 a v tabulce Pes 11). Následující skript vloží záznam do tabulky Clovek (tím se spustí trigger, který vloží jeden záznam do tabulky Pes), a potom přečte hodnoty funkcí SCOPE_IDENTITY() a @@IDENTITY a vypíše je.
-- co udelaji funkce @@IDENTITY a SCOPE_IDENTITY
declare
@identity int, @scope_identity int
insert
Clovek (Jmeno) values ('Jouda')
select
@identity = @@identity, @scope_identity = scope_identity()
select
MAX(Id) as ClovekId from Clovek
select
MAX(Id) as PesId from Pes
print
@identity
print
@scope_identity
go
Dobře se podívejme na výsledky - funkce SCOPE_IDENTITY() správně přečetla ID nově vzniklého záznamu v tabulce Clovek, zatímco funkce @@IDENTITY přečetla ID posledního vloženého záznamu v rámci příkazu! (Připomínám, že trigger je součástí příkazu, který jej vyvolal.), takže ve výsledku funkce @@IDENTITY je ID záznamu z tabulky Pes!
Myslím, že toto zjištění netřeba dále komentovat. Jen je důležité říci, že výše popsaný postup funguje při vložení JEDNOHO ZÁZNAMU! V případě vložení více záznamů by funkce SCOPE_IDENTITY zachytila hodnotu ID posledního záznamu.
