Lesson 1

As you transition from using your database to store information to using your database to manage a business, you will find that triggers are an important and useful tool for monitoring activity in your database and to prevent transactions that are not compliant with the business rules governing your database from occurring.

After completing this module, you will be able to:

1. Describe and define triggers
2. Enforce business rules
3. Create triggers
4. Modify triggers
5. Delete triggers
6. Test triggers

A TRIGGER is a type of function (or procedure) that automatically executes when certain actions are taken. They are controlled by different contexts such as

1. INSTEAD OF,
2. BEFORE, or
3. AFTER

and are usually paired with a data transaction such as INSERT, DELETE, or UPDATE.
Triggers can also be implemented at the server level to track things like logins, or prevent accidental deletions of entire databases. Procedural logic can be programmed into them and their automatic execution criteria differentiates them significantly from things such as

1. constraints,
2. functions, or
3. (stored) procedures.

Trriggers can do a lot for you, but they can also cause a lot of problems. The trick is to use them when they are the right things to use, and not to use them when they are not. Some common uses of triggers include:

1. Enforcing referential integrity: Although I recommend using Declarative Referential Integrity (DRI) whenever possible, there are many things that DRI will not do (for example, referential integrity across databases or even servers, many complex types of relationships, and so on).
2. Creating audit trails: This means writing out records that keep track of not just the most current data, but also the actual change history for each record. This may eventually become less popular with the change-data tracking that SQL Server 2008 added, but triggers are still a pretty popular choice.
3. Creating functionality similar to a CHECK constraint: Unlike CHECK constraints, this can work across tables, databases, or even servers. Substituting your own statements in the place of the action statement of a user:
   This is typically used to enable inserts in complex views.
4. In addition, you have the new, but rarer case of the Data Defi nition Language (DDL) trigger, which is about monitoring changes in the structure of your table. And these are just a few. So, with no further ado, it is time to look at exactly what a trigger is.

In the next lesson, you will learn what a trigger is and how it is used.