Purpose of Triggers in SQL Server

By: Daniel Farina

Overview

Something we must understand in order to learn about triggers is the reason why triggers exists.

What is the Purpose of Triggers?

The main purpose of triggers is to automate execution of code when an event occurs. In other words, if you need a certain piece of code to always be executed in response to an event, the best option is to use triggers. Mostly because they guarantee that the code will be executed or the event that fired the trigger will fail.

Triggers are used for several purposes:

• Produce additional checking during insert, update or delete operations on the affected table.
• They allow us to encode complex default values that cannot be handled by default constraints.
• Implement referential integrity across databases. You can read more about this in this tip: SQL Server Referential Integrity Across Databases Using Triggers.
• They allow us to control what actually happens when one performs an insert, update, or delete on a view that accesses multiple tables.
• You can calculate aggregated columns in a table using triggers.

There are two clear scenarios when triggers are the best choice: auditing and enforcing business rules. By using a trigger you can keep track of the changes on a given table by writing a log record with information about the user that made the change and what was changed.

Maybe you think you can achieve the same result in an application with a stored procedure that handles data modifications like inserts and updates. You can use the stored procedure, but in such case you will not be able to log changes that were made directly into the database without using the stored procedures.

Additional Information

• SQL Server Trigger Best Practices