SQL Triggers

Summary: In this tutorial, you will learn about SQL triggers and how to create triggers that automatically invoke a piece of code in response to an event in the table.

Introduction to SQL Triggers #

A trigger is a database object that executes a piece of code, a user-defined function, or a stored procedure in response to a specific event in a table.

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

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.
TRUNCATE – when the table is truncated. (PostgreSQL).

When you execute an INSERT, UPDATE, or DELETE statement against a table, the relational database management system (RDBMS) fires the corresponding trigger if it exists.

In some RDBMS, a trigger is also invoked as the result of executing a statement that indirectly executes the INSERT, UPDATE, or DELETE statement.

For example, MySQL has the LOAD DATA INFILE, which reads data from a text file and inserts it into a table at a very high speed, invoking 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, the TRUNCATE TABLE statement removes all rows in the table but does not invoke the BEFORE DELETE and AFTER DELETE triggers. In PostgreSQL, the TRUNCATE TABLE statement triggers a TRUNCATE trigger.

Creating triggers #

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)

In this syntax:

First, specify the name of the trigger after the CREATE TRIGGER clause.
Next, use either BEFORE or AFTER keyword to determine when the trigger should respond to a specific event e.g., INSERT, UPDATE, or DELETE.
Then, provide the name of the table the trigger is associated.
After, define the type of trigger using either FOR EACH ROW or FOR EACH STATEMENT clause.
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 user-defined function or a stored procedure:

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 vs. Statement-level triggers #

There are two types of triggers:

Row-level triggers
Statement-level triggers

A row-level trigger executes each time a row is affected by a DML statement such as INSERT, UPDATE, and DELETE.

If the statement affects 10 rows, the row-level trigger will execute 10 times. If the statement does not affect any row, the row-level trigger will not execute.

Unlike a row-level trigger, a statement-level trigger is called once regardless of how many affected rows.

Note that if the SQL statement does not affect any rows, the statement-level trigger will execute.

When creating a trigger, you can specify whether a trigger is a row-level or statement-level trigger using the FOR EACH ROW or FOR EACH STATEMENT clause espectively.

Why Use SQL Triggers #

You typically use the triggers in the following scenarios:

Loggings. Some tables have sensitive data such as customer email, employee, and salary. You want to log all the changes. In this case, you can create the UPDATE trigger to record the changes in a separate table.
Enforce complex integrity of data. You may create triggers that validate data using complex logic that you cannot accomplish with simple constraints like NOT NULL, UNIQUE, and CHECK constraints.

Creating SQL triggers #

We’ll use the employees table in the sample database for the demonstration.

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

First, create the salary_changes table to store the salary changes:

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at TIMESTAMP 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)

Try it

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.

Second, create a function that logs changes into the salary_changes table:

CREATE OR REPLACE FUNCTION log_salary_changes () 
RETURNS TRIGGER 
AS 
$$
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;
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

Try it

Note that within the trigger body, you can access the OLD and NEW variables to access the row before (OLD) and after update (NEW).

Third, create a trigger called before_update_salary that execute the log_salary_changes function to log the salary changes to the salary_changes table:

CREATE TRIGGER salary_changes
AFTER UPDATE OF salary ON employees 
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes ();Code language: SQL (Structured Query Language) (sql)

Try it

Fourth, retrieve the current salary of the employee id102:

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

 employee_id | first_name | last_name | salary
-------------+------------+-----------+---------
         110 | John       | Chen      | 8200.00

Fourth, increment the salary of the employee 110 by 5% using the following UPDATE statement:

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

Finally, check the salary_changes table to see if the trigger has been invoked:

SELECT
  employee_id,
  old_salary,
  new_salary
FROM
  salary_changes;Code language: SQL (Structured Query Language) (sql)

Output:

 employee_id | old_salary | new_salary
-------------+------------+------------
         110 |    8200.00 |    8610.00

The output indicates that the salary_changes table has a new entry. It means that the trigger has been invoked correctly.

Modify SQL Triggers #

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

The CREATE OR REPLACE TRIGGER creates a new trigger if it does not exist and changes it 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)

Removing SQL triggers #

To drop a trigger from a database, you use the DROP TRIGGER statement with the following syntax:

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

In this syntax:

First, specify the trigger’s name (trigger_name) after the DROP TRIGGER keywords.
Second, Use the IF EXISTS option to delete a trigger only if it 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.

For example, the following statement uses the DROP TRIGGER statement to remove the salary_changes trigger:

DROP TRIGGER salary_changes;Code language: SQL (Structured Query Language) (sql)

Summary #

A trigger is a database object associated with a table that automatically executes a piece of code, a user-defined function, or a stored procedure in response to an event that occurs in the table.
Use the CREATE TRIGGER statement to create a new trigger.
Use the CREATE OR REPLACE TRIGGER statement to replace a trigger.
Use the DROP TRIGGER statement to remove a trigger.

SQL Triggers

Introduction to SQL Triggers #

Creating triggers #

Row-level vs. Statement-level triggers #

Why Use SQL Triggers #

Creating SQL triggers #

Modify SQL Triggers #

Removing SQL triggers #

Summary #

Quiz #

Databases #