DML Triggers in the AdventureWorks Sample Database
This topic describes several examples of DML triggers that are included in the AdventureWorks 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
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
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
uPurchaseOrderHeaderupdate 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.
Help and Information
14 April 2006