Good article. It highlights the most common mistake made in triggers by developers coming to sql server from other databases which usually have row level triggers.
However, the example is strange. When you implement a history, the main table triggers insert old values into the history table. The opposite, where you Insert into history table, where the trigger updates the main table, is IMHO unusual and possibly wrong.
If the design is correct and history in the name is misleading, shouldn't it update Total_Pkgs?
In sqlserver I don't miss row level triggers, but I do miss before triggers for two reasons:
1. after trigger can raise an exception to prevent an operation, but client can still (auto)commit transaction with wrong data
2. insert of triggers are a pain, but can solve the problem #1
Wrong? I suppose that is a matter of perspective. I can assure you that the example, while significantly simplified over the real-life scenario, is entirely accurate. History comes in independent of the base (Shipment) information and relates to a chain of historical events for each Shipment transaction.
Allow me to elaborate. Of the approximately 50 non-report generating forms, about 50% of those represent an automated event in a shipment's history. So when you fill out those forms for a Shipment, they each generate a unique event, which may be for a package or for a consignment. Many of these events come from bar-code scanning of a shipment or package. History also comes with it a whole lot more information that is not recorded at the Shipment level (about the event). Many of the events are not triggered by an activity on a form (e.g., you can put an "OPS" comment into a shipment at any time to indicate some special condition).
Since there are many sources that generate events, and relatively few of these impact any data on the Shipment record (last status code and date/time being the exceptions), the act of creating the history fires the trigger. Furthermore, since the history drives a lot of the reporting (many reads off that table) the initial status is recorded in a staging table, and only recorded as a "last status" for the Shipment when "scooped" up in large quantities from the staging table and inserted in bulk to the history table.
Some other notations:
- The original design limit was to support 500 history events per minute, although using some clever load balancing techniques it will support much higher during peak periods (system operates 24/7).
- Events for a consignment can be recorded prior to the Shipment existing, even though there is a FOREIGN KEY relationship (parent/child) from Shipment to Shipment History. This is allowed because the first notification that a shipment has arrived (a scan) may be the box appearing on the loading dock.
- Some of the history is captured through mobile devices.
- One of the reports that must use the full history table is something that's called a "Versus Report." The idea is that some of the individual events should generally occur in a specific sequence. This report is designed to identify cases of events missing or out of sequence. That's a rather intensive event given that the history table easily holds upwards of 50M rows.
- Edit: Almost forgot. The data in history is highly time sensitive, meaning that reporting needs to be up to the minute. For example, the status POD (proof of delivery) is usually monitored quite closely (especially towards the end of the business day) due to delivery commitments that could result in a free shipment to a customer if they are not met.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]