896
Part VII: Monitoring and Auditing
Every trigger has the opportunity to ROLLBACK the transaction. If the transaction is rolled
back, then all the work done by the initial transaction and all the triggers are rolled back.
Any triggers that had not yet fi red won’t fi re because the original DML is aborted by the
ROLLBACK.Nevertheless, it is possible to designate an AFTER trigger to fi re fi rst or last in the list
of triggers. Do this only if one trigger is likely to roll back the transaction and, for per-
formance reasons, you want that trigger to execute before other demanding triggers.
Logically, however, the order of the triggers has no effect.The sp_settriggerorder system stored procedure is used to assign the trigger order
using the following syntax:sp_settriggerorder
@triggername = 'TriggerName',
@order = 'first' or 'last' or 'none',
@stmttype = 'INSERT' or 'UPDATE' or 'DELETE'The effect of setting the trigger order is not cumulative if they are both for the same
action. For example, setting TriggerOne to first and then setting TriggerTwo to
first does not place TriggerOne in second place. In this case, TriggerOne returns to
being unordered.Transaction-Aggregation Handling
Triggers can maintain denormalized aggregate data.A common example of this is an inventory system that records every individual transac-
tion in an InventoryTransaction table, calculates the inventory quantity on hand, and
stores the calculated quantity-on-hand in the Inventory table for performance.Index views are another excellent solution to consider for maintaining aggregate data. They’re docu-
mented in Chapter 45, “Indexing Strategies.”To protect the integrity of the Inventory table, implement the following logic rules when
using triggers:■ (^) The quantity on hand in the Inventory table should not be updatable by any pro-
cess other than the inventory transaction table triggers. Any attempt to directly
update the Inventory table’s quantity should be recorded as a manual adjustment
in the InventoryTransaction table.
■ (^) Inserts in the InventoryTransaction table should write the current on-hand
value to the Inventory table.
■ (^) The InventoryTransaction table should not allow updates. If an error is
inserted into the InventoryTransaction table, an adjusting entry should be
made to correct the error.
c36.indd 896c36.indd 896 7/31/2012 10:03:09 AM7/31/2012 10:03:09 AM
http://www.it-ebooks.info