SQL-Server Triggers  «Prev  Next»

Lesson 4 Enforcing business rules with triggers
Objective What is considered when enforcing business rules with triggers

Enforcing Business Rules with Triggers

Describe what should be considered when enforcing business rules with triggers.
Because triggers automatically perform an action after a change is made to your database, they are an important method for enforcing the business rules that govern your database.
Business rules govern the conditions that must be met for a company’s policies to be enforced.

Trigger guidelines

Following are some guidelines for using triggers to enforce business rules in your database:
  1. Make sure that you fully understand the rules. Many times rules are very complicated. Make sure that you understand all conditions so that even obscure cases can be supported.
  2. Determine whether a trigger is the correct way to enforce the rules. You may need to enforce the business rules with primary keys, foreign keys, constraints, stored procedures, or some other method.
  3. If a trigger is warranted, use solid coding techniques to make sure that your code is maintainable and easy to read.
  4. Fully test your triggers, especially if the rules are very complex.

Ad  
SQL-Server 2019

Transaction Flow

Triggers affect the transactional state in which they are fired. Knowing these effects can prevent conflicts with constraints, locking, and blocking on the affected tables. Every transaction invokes various checks in the following order:
  1. IDENTITY INSERT check.
  2. Nullability constraint.
  3. Data-type check.
  4. INSTEAD OF trigger execution. If an INSTEAD OF trigger exists, then execution of the DML stops here. INSTEAD OF triggers are not recursive. (Recursive triggers are covered later .)
  5. Primary-key constraint.
  6. Check constraints.
  7. Foreign-key constraints.
  8. DML execution and update to the transaction log.
  9. AFTER trigger execution.
  10. Commit transaction. Based on SQL Server’s transaction flow, keep the following points in mind:

  1. An AFTER trigger occurs after all constraints are enforced. If a constraint is violated, then AFTER triggers are not fired.
  2. An "INSTEAD OF trigger" can circumvent foreign-key violations but not nullability, data-type, or identity-column violations.
  3. The "AFTER trigger" occurs before the DML transaction is committed, so it can roll back the transaction if the data is unacceptable.

Trigger examples

Following are examples of business rules that are good candidates for enforcement with triggers:
  1. No employee can have a salary greater than $200,000 without the approval of a manager. The manager’s employee ID must be stored toprove that this salary was authorized.
  2. Whenever a sales transaction is greater than $50,000, the company president must be notified so that the salesperson can be personally congratulated.
  3. A sale cannot be made for an item that is NOT in inventory, unless the InventoryType field has a value of B.
All these rules can be enforced by using triggers. The rest of this module shows you how to do this.
In the next lesson, the creation of triggers will be discussed.