DML Triggers in the AdventureWorks2008R2 Sample Database
This topic describes several examples of DML triggers that are included in the AdventureWorks2008R2 sample database. These DML triggers are designed to enforce the business rules of Adventure Works Cycles and to help protect the integrity of its data.
To view the text of each trigger in SQL Server Management Studio
In Object Explorer, locate the table on which the trigger is defined, and expand the Triggers folder.
Right click the trigger you want, and then click Script Trigger as.
Setting a Field After an Update
uSalesOrderHeader DML Trigger
The table Sales.SalesOrderHeader contains the RevisionNumber column to reflect the number of times a particular row, excluding the Status field, has been updated. The DML trigger uSalesOrderHeader increments the RevisionNumber field by one, or sets it to one if it is null, after an update.
The uPurchaseOrderHeader DML trigger performs the same action to table Purchasing.PurchaseOrderHeader as the uSalesOrderHeader DML trigger performs on table Sales.SalesOrderHeader.
Populating a Table After Insert, and Then Setting a Field in Another Table
iPurchaseOrderDetail and uPurchaseOrderHeader DML Triggers
When data is inserted into the PurchaseOrderDetail table, the DML trigger iPurchaseOrderDetail inserts a part of the inserted data into the Production.TransactionHistory table also. Because multiple line items in PurchaseOrderDetail make up one purchase order in the PurchaseOrderHeader table, the Subtotal field in PurchaseOrderHeader must be updated when an insert occurs in PurchaseOrderDetail.The iPurchaseOrderDetail trigger aggregates the LineTotal field of PurchaseOrderDetail for a particular sales order, and sets the Subtotal field of PurchaseOrderHeader with that aggregation.
Note when the DML trigger performs an update on Purchasing.PurchaseOrderHeader, this action causes the uPurchaseOrderHeader update trigger that exists on the Purchasing.PurchaseOrderHeader table to fire. For more information about triggers that cause other triggers to fire, see Using Nested Triggers.
The DML trigger uPurchaseOrderDetail performs the same actions to the TransactionHistory and PurchaseOrderHeader tables as iPurchaseOrderDetail, but performs them when the PurchaseOrderDetail table is updated, instead of when it receives an insert.
DML triggers iWorkOrder and uWorkOrder on table Production.WorkOrder insert data to the TransactionHistory table only.