Lesson 11

This module discused how to use triggers to enforce the business rules that govern your database. Remember that triggers can lose their value, or even be harmful if they are not modified or deleted to accommodate business rule changes. You will find that many businesses change their business rules often to adapt to changing conditions. Be prepared to customize your database triggers as well, to keep up with these changes.
Having completed this module, you should be able to:

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

This module introduced you to the following terms:

1. Business rule
2. Cascade
3. Event
4. Fire
5. Nesting
6. Trigger

SQL Server triggers are special stored procedures attached to table events and cannot be executed directly, but fi re only in response to an INSERT, UPDATE, or DELETE event on a table. Users can’t bypass a trigger; and unless the trigger sends a message to the client, the end user is unaware of its actions. Developing well-behaved triggers involves understanding transaction fl ow, locking, T-SQL, and stored procedures. Triggers have a few unique elements that require careful planning, but they provide execution of complex business rules and data validation.

SQL Server triggers fire once per data-modification operation, not once per affected row. This may seem to be a limitation, but developing set-based triggers actually helps ensure clean logic and fast performance. Triggers may be created for the three data-modification commands:

1. INSERT,
2. UPDATE, and
3. DELETE.

For data integrity, sometimes a trigger is the best solution, but be aware of the potential performance impact. You should consider having business rules enforced by application code instead and only use triggers when this is not feasible.
In the next module, you will learn about SQL Server stored procedures.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
SQL Triggers - Quiz