Wenn man mit Identity-Werten arbeitet, dann kann man ganz bequem über die Funktionen @@IDENTITY und SCOPE_IDENTITY() den zuletzt vergebenen Wert erfragen. Wenn man mit Triggern arbeitet, dann kann man auf dabei auf unerwartete Schwierigkeiten stoßen. Wird in dem Trigger beispielsweise in einer Protokoll-Tabelle ein Eintrag gemacht, dann liefern die beiden genannten Funktionen unterschiedliche Werte zurück:
- @@IDENTITY liefert den zuletzt vergebenen Identity-Wert zurück. In diesem Fall die ID der Protokoll-Tabelle.
- SCOPE_IDENTITY() liefert hingegen die ID aus der „richtigen“ Tabelle.
Das ist ziemlich einleuchtend, kann aber noch komplizierter werden, wenn man mit Instead-Of-Triggern arbeitet. Für unser Beispiel stelle ich mit eine View vor, die in deren Instead-Of-Trigger die Aktion auf die Basistabelle weiterleitet, also INSERT, UPDATE oder DELETE und dann einen Eintrag in eine Protokoll-Tabelle macht.
In dieser fall sind die Ergebnisse der Funktionen anders:
- @@IDENTITY liefert den zuletzt vergebenen Identity-Wert zurück. In diesem Fall die ID der Protokoll-Tabelle. Falls auf der Tabelle ein Trigger liegt, dann diese, sonst der aus dem Trigger der View.
- SCOPE_IDENTITY() liefet hingegen NULL, weil in dem Kontext ja gar kein INSERT durchgeführt wurde, sondern nur im Instead-Of-Trigger.
- Will man sich mit der OUTPUT-Klausel behelfen, dann wird für inserted.ID immer der Wert 0 ausgegeben.
Die Schlüsse daraus mag nun jeder selber ziehen. Ich für meinen Teil lasse die Finger lieber von Triggern und setze statt dessen Stored-Procedures ein.
Anbei ein paar Samples zum selber ausprobieren:
if object_id ('protokoll') IS NOT NULL
drop table protokoll
go
if object_id ('protokoll') IS NULL
create table protokoll (
id integer identity(1,1) NOT null primary key nonclustered,
ts datetime NOT NULL default getdate(),
spid integer NOT NULL default @@SPID,
[user] sysname NOT NULL default user,
pid integer NULL,
uid integer NULL,
stmt varchar(100) NOT NULL,
comment varchar(300) NULL)
go
if object_id ('MyTriggTab') IS NOT NULL
DROP TABLE MyTriggTab
go
CREATE TABLE MyTriggTab (
id integer identity(1,1) NOT null primary key nonclustered,
comment varchar(300) NULL)
go
CREATE TRIGGER T1 ON MyTriggTab
AFTER INSERT
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'AFTER INSERT', @@SPID, user_id()
go
CREATE TRIGGER T2 ON MyTriggTab
AFTER UPDATE
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'AFTER UPDATE', @@SPID, user_id()
go
CREATE TRIGGER T3 ON MyTriggTab
AFTER DELETE
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'AFTER DELETE', @@SPID, user_id()
go
INSERT INTO MyTriggTab(comment) VALUES ('Test');
SELECT 'INSERT INTO MyTriggTab' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
UPDATE MyTriggTab set comment = 'Hello';
SELECT 'UPDATE MyTriggTab' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
DELETE MyTriggTab WHERE ID = SCOPE_IDENTITY();
SELECT 'DELETE FROM MyTriggTab' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
go
IF object_id ('MyTriggView') IS NOT NULL
DROP VIEW MyTriggView
go
CREATE VIEW MyTriggView (ID, info)
AS
SELECT TOP(3) ID, comment
FROM MyTriggTab
ORDER BY ID DESC
go
CREATE TRIGGER T4 ON MyTriggView
INSTEAD OF INSERT
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'INSTEAD OF INSERT', @@SPID, user_id()
INSERT INTO MyTriggTab(comment)
SELECT inserted.info
FROM inserted
go
CREATE TRIGGER T5 ON MyTriggView
INSTEAD OF UPDATE
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'INSTEAD OF UPDATE', @@SPID, user_id()
UPDATE MyTriggTab
set comment = inserted.info
FROM MyTriggTab JOIN inserted ON MyTriggTab.ID=inserted.ID;
go
CREATE TRIGGER T6 ON MyTriggView
INSTEAD OF DELETE
AS
INSERT INTO protokoll (stmt, pid, uid)
SELECT 'INSTEAD OF DELETE', @@SPID, user_id()
DELETE FROM MyTriggTab
FROM MyTriggTab JOIN deleted ON MyTriggTab.ID=deleted.ID;
go
INSERT INTO MyTriggView(info) VALUES ('View-Test');
SELECT 'INSERT INTO MyTriggView' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
UPDATE MyTriggView set info = 'Hallo';
SELECT 'UPDATE MyTriggView' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
DELETE MyTriggView WHERE ID = (SELECT MIN(ID) FROM MyTriggView);
SELECT 'DELETE FROM MyTriggView' as "Statement",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY"; –> liefert ID aus Protokoll-Tabelle
go
DECLARE @outtable TABLE(ID integer);
INSERT INTO MyTriggView(info)
OUTPUT inserted.ID INTO @outtable
VALUES ('View-Test');
SELECT 'INSERT INTO MyTriggView' as "Statement",
ID as "OUTPUT",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY" –> liefert ID aus Protokoll-Tabelle
FROM @outtable
go
SELECT * FROM MyTriggView;
SELECT * FROM MyTriggTab
SELECT * FROM protokoll