Using Triggers
(Page 1 of 4 )
Last week, we learned how to modify triggers. This week, we will delve more deeply into using triggers. The fifth of five parts, it is excerpted from chapter nine of the book
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).
Using Triggers
In SQL Server, DML triggers may have the following roles:
- To enforce data integrity, including referential integrity and cascading deletes
- To enforce business rules too complex for Default and Check constraints
- To log data changes and send notification to administrators via e-mail
- To maintain derived information (computed columns, running totals, aggregates, and so on)
Triggers can be implemented to replace all other constraints on a table. A typical example is the use of a trigger to replace the functionality enforced by a foreign key constraint.
It is possible to implement cascading deletes using triggers. For example, if you do not have a foreign key between the Inventory and InventoryProperty tables, you might implement a trigger to monitor the deletion of Inventory records and to delete all associated InventoryProperty records.
Check and Default constraints are limited in that they can base their decision only on the context of current records in the current tables. You can implement a trigger that functions in a manner similar to Check constraints and that verifies the contents of multiple records or even the contents of other tables.
Triggers can be set to create an audit trail of activities performed on a table. For example, you might be interested in obtaining information on who changed the contents of, or specific columns in, the Lease table, and when that user made the changes.
It is possible to create a trigger to notify you when a specific event occurs in the database. For example, in a technical support system, you might send e-mail to the person responsible for dispatching technical staff, to inform that person that a request for technical support has been received. In an inventory system, you might automatically generate a purchase order if the quantity of an inventory item falls below the specified level.
Triggers are suitable for computing and storing calculated columns, running totals, and other aggregates in the database. For example, to speed up reporting, you might decide to keep a total of ordered items in an order table.
DDL triggers are presenting us with completely new area of opportunities. They could be used:
- To establish a traceable record of schema changes to database objects for auditing purposes
- To log security changes on a server
- To prevent certain types of schema changes
- To ensure that a set of database objects is identical across a group of databases or servers
Next: Cascading Deletes >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 5 
