Setting Limits with Triggers
Dr. Tom Moreau found an interesting problem on the Usenet. The poster wanted to limit the number of detail rows for an order to a maximum of four. As you might have guessed, Dr. Tom took the question as a challenge: "I thought it would be an interesting exercise to implement the answer by building a trigger."
Triggers have been a part of SQL Server since the beginning (well, at least since Microsoft "inherited" SQL Server from Ashton-Tate and Sybase; see http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q45581, for example, for an old KB article about triggers in Version 4.2!). Originally, triggers were used to enforce referential integrity (RI), since declarative RI (DRI) didn't even exist until 6.5. And even then, we didn't have what most people would consider "complete" DRI until SQL2K with its support for cascaded deletes and updates. It seems to me, though, that today triggers are primarily being used to enforce business rules that can't be enforced with check constraints.
So let's have a go at the business problem at hand—limiting the number of order detail rows for an order to just four. How many of you would jump right in and go with something like Listing 1?
Listing 1. Limiting Order Details to four rows per OrderID, first try.
create trigger tri_OrderDetails on [Order Details] for insert as if (@@ROWCOUNT) > 4 begin raiserror ('Cannot insert more than four order details', 16, 1) rollback tran return end go
What's wrong with this picture? Well, what this trigger does is limit the total number of rows inserted by the INSERT statement to no more than four, regardless of the OrderID. Therefore, if you were inserting two rows for one order and three for another via a single INSERT SELECT statement, the statement would fail—even though it doesn't violate the business rule. Your code needs to break down the number of rows per order. Listing 2 shows our second try.
Listing 2. Limiting Order Details to four rows per OrderID, second try.
create trigger tri_OrderDetails on [Order Details] for insert as if exists (select * from inserted group by OrderID having count (*) > 4) begin raiserror ('Cannot have more than four order details per order', 16, 1) rollback tran return end go
Much better, but… uh… this won't work either, and if you don't see why immediately, take heart: The problem is subtle. What if you did five single-row INSERTs for a given OrderID? The trigger code would never trap the problem, since it inspects only the inserted table, not at any existing rows in the Order Details table. [The "inserted" table is one of two special tables used in trigger statements (the other one being the "deleted" table) that SQL Server automatically creates and manages. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions, but you can't alter the data in the tables directly. The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table. Note that, in SQL2K, you can't use text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers.—Ed.]
Okay, so if you have to look at the whole picture, then it really doesn't matter if you look at the inserted table at all. What really counts is the entire Order Details table after the INSERT, since it has the existing rows plus the new ones. Listing 3 shows a more "holistic" approach.
Listing 3. Limiting Order Details to four rows per OrderID, third try.
create trigger tri_OrderDetails on [Order Details] for insert as if exists (select * from [Order Details] group by OrderID having count (*) > 4) begin raiserror ('Cannot have more than four order details per order', 16, 1) rollback tran return end go
Done. If you run this on the Northwind database, no INSERT statement will violate the business rule. However, the first time you use it, your transaction will get rolled back since there are already orders for which there are more than four rows. Now, if the rule applies to existing orders as well as new ones, you'll first have to cleanse the data before putting the trigger to use. Can you pick out another flaw?
Northwind isn't a big database, and if you don't have that many transactions, the performance will be satisfactory. But what if you had millions of rows and hundreds of users? Be prepared to wait…
What you want is to limit your search only to those rows whose OrderIDs were involved in the INSERT statement. That information is contained in the inserted table. If you do a SELECT DISTINCT on the OrderID, that will give you the list of OrderIDs. Join that onto the Order Details table and apply the same criteria as in Listing 3. Listing 4 is the result.
Listing 4. Limiting Order Details to four rows per OrderID, fourth try.
create trigger tri_OrderDetails on [Order Details] for insert as if exists (select * from [Order Details] od join (select distinct OrderID from inserted) i on i.OrderID = od.OrderID group by od.OrderID having count (*) > 4) begin raiserror ('Cannot have more than four order details per order', 16, 1) rollback tran return end go
Now your code does limit its check to just those OrderIDs that arose from the INSERT. You have the correct business rule enforcement, coupled with performance. But guess what? This still isn't quite the right solution.
This trigger is set up just for INSERTs. What if you were to move rows from one OrderID to another? That's done through an UPDATE statement. Therefore, you'll either need an UPDATE trigger or you'll have to modify the existing INSERT trigger to handle both INSERTs and UPDATEs. As it turns out, the guts of the INSERT trigger will do exactly what you want for both INSERTs and UPDATEs, so all you have to do is alter the FOR clause of the CREATE TRIGGER statement.
Another thing you should look at from a performance standpoint is to check @@ROWCOUNT right at the beginning of the trigger. If the value is 0, that means no rows were affected by the triggering statement and executing the rest of the trigger code is unnecessary. (You should do this in any trigger.) After that, you should also check whether OrderID was part of the UPDATE. If not, there's no need to execute the rest of the trigger code. The final code—I mean it this time—is shown in Listing 5.
Listing 5. Limiting Order Details to four rows per OrderID—fifth try's the charm.
create trigger triu_OrderDetails on [Order Details] for insert, update as if @@ROWCOUNT = 0 return if not update (OrderID) return if exists (select * from [Order Details] od join (select distinct OrderID from inserted) i on i.OrderID = od.OrderID group by od.OrderID having count (*) > 4) begin raiserror ( 'Cannot have more than four order details per order', 16, 1) rollback tran return end go
An IF UPDATE() is true for all INSERT statements and for those UPDATE statements where the column being tested is in the SET clause.
So what are the lessons learned? Don't be so quick to jump to a solution. Handle single and multi-row scenarios. Make sure that you cover both UPDATE and INSERT situations. Limit yourself just to the relevant rows. Add a check of @@ROWCOUNT, as well as one for UPDATE() of the relevant column(s) to eliminate needless execution of code.
Coda: I had a great exchange with Jimmy Nilsson, a fellow Pinnacle author (see his articles in Visual Basic Developer), and he posed to me virtually the same problem as discussed here. However, he asked if a scalar User-Defined Function (UDF) could be used. Having written this article, I immediately replied that I didn't think it could be done and sent him the code I've shown here. Then, I got curious. I created a simple UDF that counted the number of rows for a given OrderID as follows:
create function dbo.OrderDetailCount (@OrderID int) returns int as begin return (select count (*) from [Order Details] where OrderID = @OrderID) end
Then, I altered the Order Details table, adding a check constraint to use the UDF.
alter table [Order Details] with nocheck add constraint CKOrderDetails check (dbo.OrderDetailCount (OrderID) < 5)
I used the NOCHECK option, since some existing orders would violate the constraint. Finally, I ran an INSERT that I knew would violate the constraint.
insert [Order Details] (OrderID, ProductID, UnitPrice, Quantity) values (10255, 1, 10.00, 5)
I was pleasantly surprised to get a 547 error, indicating a column check violation. Sweet!
I suppose the moral of this coda is that when you have a hammer, everything looks like a nail. If you've been using SQL Server as long as I have, you're probably used to creating triggers for everything from doing RI to taking the warts off of your feet. I have to remind myself to take my own advice, particularly when it comes to a product as powerful as SQL Server 2000. I always tell my students to assume something can be done, until you prove that it can't. Thanks, Jimmy, for keeping me honest. I owe you one.
To find out more about SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the April 2003 issue of SQL Server Professional. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.