Identities and Triggers—A Lethal Mix

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

Top 5 B2B SaaS Marketing Agencies for 2023

In recent years, the software-as-a-service (SaaS) sector has experienced exponential growth as more and more companies choose cloud-based solutions. Any SaaS company hoping to stay ahead of the curve in this quickly changing industry needs to invest in effective marketing. So selecting the best marketing agency can mean the difference

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.