By: Daniel Farina
Overview
There is often the need to perform certain database actions in response to a specific event occurring, like when a row is added to a table. In most database systems, including SQL Server, there is the ability to create "Triggers" that fire when a given event occurs either at the database level or at the instance level. In this tutorial learn about SQL Server triggers and how these can be used.
Here is an example of a how a trigger could be used. Every time data is changed in a table, a history record is saved of the previous values into another table. By using a trigger, this history record will be created regardless of how the data was changed in the table. It could be from a direct update or by an application, it doesn't matter because the trigger will always fire and capture the history record. This also eliminates having to write the logic for this history record in several different places in your applications.
Explanation
A trigger is a piece of procedural code, like a stored procedure, which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name a few: insertion of a row in a table, a change in a table structure and even a user logging into a SQL Server instance.
There are three main characteristics that make triggers different than stored procedures:
- Triggers cannot be manually executed by the user
- There is no chance for triggers to receive parameters
- You cannot commit or rollback a transaction inside a trigger
The fact that it's impossible to use parameters on triggers is not a limitation to receive information on the firing event. As you will see further on in this tutorial, there are alternatives to obtain information about the firing event.
Topics to be covered
In this tutorial we will cover from top to bottom the following topics:
- Working with SQL Server Triggers Tutorial
- Types of SQL Server Triggers
- Purpose of Triggers
- Pros and Cons of Triggers
- SQL Server DML Triggers
- SQL Server Triggers INSERTED Pseudo Table
- SQL Server Triggers DELETED Pseudo Table
- SQL Server Trigger After Insert
- SQL Server Trigger After Update
- SQL Server Trigger After Delete
- SQL Server Trigger Instead of Insert
- SQL Server Trigger Instead of Update
- SQL Server Trigger Instead of Delete
- Single Trigger for Insert Update and Delete in SQL Server
- Nested Triggers
- SQL Server DDL Triggers
- SQL Server Database Trigger Example
- SQL Server Trigger Functions
- SQL Server Logon Trigger Example
- Enabling and Disabling SQL Server Triggers
- Useful Queries to Deal with SQL Server Triggers
Last Update: 8/2/2021
