A Trigger is a Stored Procedure executed upon the modification to a database table. Triggers can be set to occur on INSERT, DELETE, and UPDATE of a table row, and can even be set to happen before or after the change occurs. Upon a change to a table that has a trigger the body of the Trigger is fired performing its functionality. This allows the database manager the ability to add in checks on data validity as well as modifying additional tables if needed.
Often Triggers are used for many functions the most common being:
- Relationship Enforcement
By use of triggers you can create complex rules for data integrity. If you find that checks and reference constraints are not enough a trigger gives you the ability to define your complex rule.
- Cascading Operations
By use of triggers you can cascade impacts from a change to a table throughout the rest of the database. For example if a salesperson is deleted or set to inactive you may want to change the sales rep who is in charge of their accounts.
- Audit Changes
By use of triggers you can limit changes that occur to the database by disallowing updates and changes that should not be permitted.
Although triggers can be helpful in enforcing the integrity of your database there are 2 main disadvantages to using triggers.
- Activity Masking
By containing business logic in the database by use of triggers then activity can be masked by the end user. Meaning that a user could mean to make a simple change to a table and instead causes changes throughout the database as they were unaware of the triggers.
- Potential Performance Impact
Triggers if mismanaged can have high performance impacts on your database. As triggers are executed on every row modification they increase activity and if a Trigger modifies a table that has another trigger they can cause large cascades of activity.