SQL Triggers

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;

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;

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)
);

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;

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;

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

UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    employee_id = 110;

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

SELECT 
    *
FROM
    salary_changes;

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;

Delete triggers

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

DROP TRIGGER [IF EXISTS] trigger_name;

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;