A trigger is a SQL Server utility that causes an event to fire when data in a table is inserted, updated, or deleted. Triggers are fired according to specifications made by the SQL Server programmer. For example, you might specify that a trigger will fire when the Salary column in the Employees table is updated.

Triggers are an important tool for enforcing business rules, because they can be set to fire automatically when potential changes to your table conflict with established business rules. Triggers are set within the database rather than within a particular application, which means:

1. Triggers will fire regardless of what application generates the request, which increases your ability to enforce business rules.
2. When a business rule changes, you need to change the trigger only once.

A trigger will not fire when the requesting Transact-SQL statement produces an error, such as a primary key or syntax violation. You can use this restriction to control when triggers are fired. For example, you could specify that an error will be raised when the Salary column in the Employees table is greater than $100,000. In this case, the trigger will be fired, but no action will be taken.

When triggers are nested, one sets off another, and so on. Triggers can be nesting up to 16 levels deep. The nesting function can be turned off, or the number of levels can be decreased, by using the sp_configure system procedure.

A cascade is a series of updates or deletes invoked by a trigger. For example, you can create a trigger that fires when an employee is deleted from the database. However, when the employee is deleted, you probably don’t want his or her information stored in all the other tables that contain the employee’s ID. In this situation, the trigger can perform a cascade of deletes so that there is no trace of the employee in any table.
In the next lesson, the different types of triggers will be discussed.