Overview
To learn about triggers, we must understand why triggers exist.
Explanation
What is the Purpose of Triggers?
The main purpose of triggers is to automate the 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, 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 who 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 a case, you will not be able to log changes that were made directly into the database without using the stored procedures.
Additional Information
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
