Triggers
Learn how to perform automatic operations by using triggers after changes to a table.
We'll cover the following
Triggers are a special type of stored procedure that is automatically called when a certain action is performed on a table. For example, we can use triggers to perform an action when a row is inserted into a table, deleted from it, or data is modified.
Triggers are similar to functions and stored procedures in the sense that they have a body that is executed when the trigger is called.
The AFTER
triggers
Triggers are called when there is a change in a table’s state. For example, a trigger may be executed after a row is deleted from a table.
Syntax
There are three types of AFTER
triggers:
-
AFTER DELETE
means that this trigger is executed when a row is deleted from theTableName
table:CREATE TRIGGER TriggerName ON TableName AFTER DELETE AS [Trigger body]
-
AFTER INSERT
means that the trigger is called after a new row is inserted into a table:CREATE TRIGGER TriggerName ON TableName AFTER INSERT AS [Trigger body]
-
AFTER UPDATE
means the trigger is called after table data is modified:CREATE TRIGGER TriggerName ON TableName AFTER UPDATE AS [Trigger body]
Example
Let’s consider a situation where we have a table called Payments
, created using the following query:
CREATE TABLE dbo.Payments
(
Id INT PRIMARY KEY IDENTITY(1, 1),
FromWalletId INT NOT NULL,
ToWalletId INT NOT NULL,
Amount DECIMAL NOT NULL,
Date DATETIME NOT NULL
);
We want to create a basic logging system that will capture every action that is done to the table. To store our logs, we’ll create the Logs
table which needs to have two columns:
Id
Message
To achieve our objective, we need three triggers: AFTER INSERT
, AFTER DELETE
, and AFTER UPDATE
. Each of them will insert a corresponding message into the Logs
table. To test whether the triggers are working, we can insert into, delete from, and update the Payments
table:
Get hands-on with 1200+ tech skills courses.