The Comprehensive Guide to SQL Triggers

Summary: in this tutorial, you will learn about the SQL triggers concept and how to develop simple triggers in the database system.

Introduction to SQL Triggers

A trigger is a piece of code executed automatically in response to a specific event occurred on a table in the database.

A trigger is always associated with a particular table. If the table is deleted, all the associated triggers are also deleted automatically.

A trigger is invoked either before or after the following event:

INSERT – when a new row is inserted
UPDATE – when an existing row is updated
DELETE – when a row is deleted.

When you issue an INSERT, UPDATE, or DELETE statement, the relational database management system (RDBMS) fires the corresponding trigger.

In some RDMBS, a trigger is also invoked in the result of executing a statement that calls the INSERT, UPDATE, or DELETE statement. For example, MySQL has the LOAD DATA INFILE, which reads rows from a text file and inserts into a table at a very high speed, invokes the BEFORE INSERT and AFTER INSERT triggers.

On the other hand, a statement may delete rows in a table but does not invoke the associated triggers. For example, TRUNCATE TABLE statement removes all rows in the table but does not invoke the BEFORE DELETE and AFTER DELETE triggers.

Trigger creation statement syntax

To create a trigger, you use the following statement:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax in more detail:

First, specify the name of the trigger after the CREATE TRIGGER clause.
Next, use either BEFORE or AFTER keyword to determine when to the trigger should occur in response to a specific event e.g., INSERT, UPDATE, or DELETE.
Then, specify the name of the table to which the trigger binds.
After, specify the type of trigger using either FOR EACH ROW or FOR EACH STATEMENT. We will discuss more on this in the next section.
Finally, put the logic of the trigger in the BEGIN ... END block.

Besides using the code in the BEGIN END block, you can execute a stored procedure as follows:

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
EXECUTE stored_procedure_name;Code language: SQL (Structured Query Language) (sql)

Row level trigger vs. statement level trigger

There are two types of triggers: row and statement level triggers.

A row level trigger executes each time a row is affected by an UPDATE statement. If the UPDATE statement affects 10 rows, the row level trigger would execute 10 times, each time per row. If the UPDATE statement does not affect any row, the row level trigger is not executed at all.

Different from the row level trigger, a statement level trigger is called once regardless of how many rows affect by the UPDATE statement. Note that if the UPDATE statement did not affect any rows, the trigger will still be executed.

When creating a trigger, you can specify whether a trigger is row or statement level by using the FOR EACH ROW or FOR EACH STATEMENTrespectively.

SQL trigger usages

You typically use the triggers in the following scenarios:

Log table modifications. Some tables have sensitive data such as customer email, employee salary, etc., that you want to log all the changes. In this case, you can create the UPDATE trigger to insert the changes into a separate table.
Enforce complex integrity of data. In this scenario, you may define triggers to validate the data and reformat the data if necessary. For example, you can transform the data before insert or update using a BEFORE INSERT or BEFORE UPDATE trigger.

SQL trigger example

We will use the employees table in the sample database for the demonstration.

Suppose we want to log the changes of values in the salary column. To do this, we create a separate table for storing the changes and use a trigger to insert the changes into this table.

The following statement creates the salary_changes table.

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_salary DECIMAL(8 , 2 ),
    new_salary DECIMAL(8 , 2 ),
    PRIMARY KEY (employee_id , changed_at)
);Code language: SQL (Structured Query Language) (sql)

The salary_changes table logs the employee id, old salary, new salary and the time of changes. Note that the change_at column uses the current time as the default to log the time when the change occurs.

The following before_update_salary trigger logs the salary changes to the salary_changes table.

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary <> OLD.salary THEN
	INSERT INTO salary_changes(employee_id,old_salary,new_salary)
        VALUES(NEW.employee_id,OLD.salary,NEW.salary);
    END IF;
END;
Code language: SQL (Structured Query Language) (sql)

In the body of the trigger, we insert the changes if the new salary is different from the old one.

Note that within the trigger body, we use the OLD and NEW keywords to access columns in the rows affected by a trigger.

Let’s test the trigger by raising the salary of the employee whose id is 102 5%.

First, check the current salary of the employee 102:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

Second, raise the salary by 5% by issuing the following UPDATE statement.

UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

Third, check the salary_changes table to see if the trigger has been invoked.

SELECT 
    *
FROM
    salary_changes;Code language: SQL (Structured Query Language) (sql)

As you see, the salary_changes table has a new entry. It means that the trigger has been invoked correctly.

Modify triggers

To change the trigger definition, you use the CREATE OR REPLACE TRIGGER statement.

Basically, the CREATE OR REPLACE TRIGGER creates a new trigger if it does not exist and changes the trigger if it does exist.

The CREATE OR REPLACE TRIGGER statement is similar to the CREATE TRIGGER statement as follows:

CREATE OR REPLACE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

Delete triggers

To delete a trigger, you use the DROP TRIGGER statement as follows:

DROP TRIGGER [IF EXISTS] trigger_name;Code language: SQL (Structured Query Language) (sql)

The IF EXISTS option allows you to delete a trigger if the trigger exists. If the trigger does not exist, then the statement does nothing. However, if you don’t have the IF EXISTS option, the database system may issue an error if you try to drop a non-existing trigger.

Again, if you drop a table, all triggers associated with the table are also deleted. The following statement deletes the before_update_salary trigger:

DROP TRIGGER IF EXISTS before_update_salary;Code language: SQL (Structured Query Language) (sql)

Now you should have a good understanding of the SQL triggers and know how to create a trigger in the database system.