Triggers

Learn how to perform automatic operations by using triggers after changes to a table.

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 the TableName 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.