he identity property is a useful tool in designing databases. However, you can encounter serious problems with using it when combined with triggers. This problem was brought to my attention by readers.

Every table can have one column defined with the identity property. The column type must be either decimal, int, numeric, smallint, or tinyint. Instead of having to provide a value for this column when inserting a record, the system will generate a value for you. You specify the starting value and the increment to be used. For example, the orderid field in the order table of the Northwind database would be defined in SQL as orderid int identity(1,1).

It is quite common to use an identity field as a primary key in cases where there is otherwise no unique column or combinations of columns. Taking the orders table as an example, it is hard to come up with a combination of columns that would make an order unique. It is also used when two tables are joined in a foreign key relationship. If the natural primary key is too large, an identity field is used to maintain the relationship. Continuing with the orders table as an example, suppose you decided to use a combination of the employee id, customer id, and the order date as your primary key. You would then have to store those fields in every record of the order detail table. It is easier to store just one field, the order id, which is four bytes in the table, than the four fields that together take up 17 bytes. It also simplifies the joins between the two tables.

If you are inserting a new order into the database, you will first insert a row into the orders table. You then need to use the value of the identity column for that row to insert the various order detail records you need to create. The system variable @@identity stores the identity value of the last identity created by an insert. So if you wanted to insert a row into the orders table, you could use this code:

declare @myidentity intinsert into orders(CustomerID, EmployeeID, OrderDate) values ('ALFKI',1,getdate())select @myidentity = @@identityinsert into [order details] (OrderID, ProductID, UnitPrice, Quantity, Discount) values (@myidentity, 1, 5, 20, .05)

What happens if there is a trigger on the orders table that inserts a record into an audit table? Once another insert occurs, the @@identity value is reset. You can see this by creating this trigger in the Northwind database and then attempting the previous insert again.

create table audit_orders(	actionid int not null,	orderid int not null,	action char(10) not null,	who varchar(30) not null,	actiontime datetime not null,	constraint pk_audit_orders primary key(actionid))create trigger ti_orders on ordersfor insertasdeclare @actionid intselect @actionid = coalesce( max(actionid)+ 1,0) from audit_ordersinsert into audit_orders(actionid, orderid, action, who, actiontime)	select 	@actionid, OrderID, 'NEW', user_name(), getdate()	from inserted

You will get this error message:

Server: Msg 515, Level 16, State 2, Line 9Cannot insert the value NULL into column 'OrderID', table 'Northwind.dbo.Order Details';column does not allow nulls. INSERT fails.The statement has been terminated.

What’s worse is when the insert in the trigger is to a table that has an identity field?then you will actually get a number returned from the @@identity variable. However, it will be set to the value of the insert of the second table. So in the example, the insert into the order details table might work. However, it will contain the wrong order id number. In effect, you will be adding to some other order. You can try this for yourself by changing the audit_orders table to use an identity column for the actionid field.

I have been told that Microsoft is working on a fix that will be in the first service pack for version 7. Meanwhile, the simplest solution is to avoid the situation. If this is not an option, you can refer to the Microsoft Knowledge Base article, Q163446, which discusses several possible solutions.