Triggers

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: