FIX: @@IDENTITY Returns NULL If Trigger Inserts into Table Without IDENTITY Column

ID: Q199688

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 53856 (SQLBUG_70)

SYMPTOMS

In SQL Server 7.0, a SELECT @@IDENTITY statement returns NULL after an insert into a table with a trigger, and that trigger inserts into another table that does NOT have an IDENTITY column. In SQL Server 6.5, the SELECT @@IDENTITY statement returns the value inserted into the IDENTITY column of the base table. The SQL Server 7.0 Books Online incorrectly states that the SQL Server 7.0 behavior is correct.

WORKAROUND

To retrieve the identity value inserted into the base table, please see the following article in the Microsoft Knowledge Base:

Q163446 PRB: Guarantee @@IDENTITY Value on a Per Table Basis

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

The @@IDENTITY variable is a per-user variable that contains the value of the IDENTITY field from the last insert performed by the user. The following table summarizes the @@IDENTITY value for SQL Server versions 6.5 and 7.0:

Action SQL Server 6.5 @@IDENTITY value SQL Server 7.0 @@IDENTITY value

INSERT to table A
No IDENTITY Col,
No Trigger NULL NULL

INSERT to table B
w/ IDENTITY Col,
No Trigger b b

INSERT to table B
w/ IDENTITY Col,
Trigger inserts to
table C, w/ IDENTITY col c c

INSERT to table B
w/ IDENTITY Col,
Trigger inserts to
table D, No IDENTITY col b NULL

In the table above, "b" is the value inserted into the IDENTITY column for table B, and "c" is the value inserted into the IDENTITY column for table C.

The SQL Server 6.5 behavior is useful in cases where an INSERT trigger inserts rows into an audit table that does not have an IDENTITY column. This allows the user to retrieve the identity value inserted into the base table by simply using a SELECT @@IDENTITY statement. In SQL Server 7.0, this incorrectly returns NULL.

Action	SQL Server 6.5 @@IDENTITY value	SQL Server 7.0 @@IDENTITY value
INSERT to table A No IDENTITY Col, No Trigger	NULL	NULL
INSERT to table B w/ IDENTITY Col, No Trigger	b	b
INSERT to table B w/ IDENTITY Col, Trigger inserts to table C, w/ IDENTITY col	c	c
INSERT to table B w/ IDENTITY Col, Trigger inserts to table D, No IDENTITY col	b	NULL

Additional query words: prodsql


Keywords          : kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: June 7, 1999