Blog

We’ll see what are database TRIGGERShow they can help us maintain the integrity of our database and how to create them with SQL step by step.

Content

• What is an SQL TRIGGER?
• What is an SQL TRIGGER used for?
• How to create TRIGGER SQL
  • Structure of a trigger
  • New and old data. The OLD and the NEW
  • Example of a simple TRIGGER
  • Example 2 of a simple TRIGGER
• Summary and tips for using TRIGGER:
• Extra: Creation and use of variables in a TRIGGER

What is an SQL TRIGGER?

A TRIGGER, too known as triggeris a kind of SQL programming script for database. TRIGGERS are procedures that will be executed according to our indications when operations are carried out on the information in the database. These operations can be update (UPDATE), insertion (INSERT) and deletion (DELETE).

The TRIGGER can also modify the information from the database and even stop the execution of bad queries.

TRIGGERS are procedures that will be executed according to our indications when operations are carried out on the information in the database. These operations can be update (UPDATE), insertion (INSERT) and deletion (DELETE).

What is an SQL TRIGGER used for?

TRIGGERS are one of the most useful features we have when designing and maintaining databases. Thanks to them we will be able to implement certain characteristics of our database without the need to develop programming in other external languages.

Imagine, for example, that you want to duplicate in a second table all the information that is inserted in another. There is no way to tell the database to do this automatically. However, thanks to a TRIGGER that is executed after an INSERT, we can insert that information into the second table, all without the user/programmer who launched the INSERT having to do anything.

The possibilities of the TRIGGER are very large, and we must always keep them in mind as a resource to manipulate the information of INSERT, UPDATE or DELETE.

How to create TRIGGER SQL

Create a TRIGGER with SQL It’s simple, almost all database systems (DBMS) are prepared for it, and some can even incorporate an assistant to guide us through the process. Next I will explain how to create it step by step, you will see how in 5 minutes you will be able to create your first TRIGGER.

Structure of a trigger

The creating a TRIGGER Follow the basics of creating a stored procedure or a stored function. First we declare a delimiter and use the CREATE statement:

DELIMITER $$
CREATE TRIGGER trigger_historico

In these SQL lines I have created a TRIGGER with the name trigger_historic. This name will help me later to identify it, thus being able to delete it or search for it in the database.

Next we must indicate WHEN THE TRIGGER SHOULD BE EXECUTED. The possible indications at this point are AFTER or BEFORE to indicate if the trigger will be executed BEFORE or AFTER the order launched by the user. These commands will be: INSERT, DELETE or UPDATE. Finally we will indicate on which table it will act.

We are going to complete the previous code indicating that the execution will be after an INSERT In the table user:

DELIMITER $$
CREATE TRIGGER trigger_historico
AFTER INSERT ON usuario
FOR EACH ROW

As you can see I have added the last two lines: the first indicates that the execution will be after an insertion in the user table, while the second is part of the structure of a TRIGGER y indicates that it should be applied to each inserted row.

Finally, using the BEGIN and END commands, we indicate the lines of SQL code that the TRIGGER will execute:

DELIMITER $$
CREATE TRIGGER trigger_historico
AFTER INSERT ON usuario
FOR EACH ROW
BEGIN
//líneas de código SQL que se ejecutarán
END; $$

New and old data. The OLD and the NEW

So that our lines of SQL code can access the information involved in the TRIGGER, that is, the information affected by an UPDATE, INSERT or DELETE, we have two special variables NEW and OLD.

The NEW variable of a TRIGGER

NEW is the variable that stores the new information provided by the query to the database, that is, each one of the rows involved in an INSERT OR UPDATE. If, for example, an INSERT has been carried out, thanks to NEW we will be able to access the data entered for each column of the table. NEW.name, for example, stores the information in the name column that the new record inserted in the table will have.

Note that NEW will not be available on all TRIGGER types. Specifically, the TRIGGERs related to a DELETE will not have information in this variable since we will only have old information that is deleted.

The OLD variable of a TRIGGER

OLD is the variable that stores the old information related to the query that the TRIGGER executes. This information is made up of the rows that are going to be deleted or modified. In a DELETE for example, OLD will have the information of all the columns of the deleted records.

Just as NEW is not available for all the TRIGGERs that we create, the OLD variable cannot be used for an INSERT, since in this there is only new information that is going to be inserted into the database.

Example of a simple TRIGGER

We are going to solve a TRIGGER that allows us to keep a copy of all the clients that are inserted in a database of an online store. For this we will have two tables: client and client_historico. Thus, the following TRIGGER will insert all the customer information (name, ID, address) plus an extra column: fecha_registro.

DELIMITER $$
CREATE TRIGGER trigger_cliente_historico 
AFTER INSERT ON cliente
FOR EACH ROW
BEGIN 
   INSERT INTO cliente_historico(nombre, dni, direccion)
   VALUES (NEW.nombre, NEW.dni, NEW.direccion, CURDATE());
END; $$

Example 2 of a simple TRIGGER

The following TRIGGER will insert a new value into a unique identifier field after a data update in the table Web user:

DELIMITER $$
CREATE TRIGGER trigger_usuario_identificador
AFTER UPDATE ON usuario
FOR EACH ROW
BEGIN 
   UPDATE usuario SET identificador = CONCAT(NEW.id, '_', NEW.nombre, '_', NEW.dni)
   WHERE id = OLD.id;
END; $$

Summary and tips for using TRIGGER:

In short, a TRIGGER is used to:

• Execute SQL code when an event occurs specifically: INSERT, UPDATE or DELETE
• Help maintain the integrity of information
• Manipulate the information of a specific query BEFORE or AFTER its execution

The structure of a trigger in order is as follows:

1. Define a symbol or set of symbols that will be the delimiter for our programming. For this we will use the DELIMITER statement.
2. Use the CREATE SQL command to create the trigger and associate a name with it: CREATE TRIGGER trigger_name
3. Indicate an event on a table that will produce the execution of the trigger: AFTER/BEFORE DELETE/UPDATE/INSERT ON table
4. Write FOR EACH ROW to be executed for each record inserted/updated/deleted.
5. Delimit the programming zone with BEGIN and END.
6. Finally, close the TRIGGER declaration by using the symbol or set of symbols that we indicated at the beginning.

When we use TRIGGER’s in our databases we must take several things into account:

• We must be very careful that one TRIGGER does not execute another and this triggers the previous one. It would be an infinite loop of execution.
• It is possible to have several triggers that are executed in the same event and moment on the same table. The idea is to break code into small chunks to avoid cumbersome maintenance.
• If you go to overwrite a TRIGGER use the command DROP TRIGGER IF EXISTS trigger_name; on the next line to the DELIMITER delimiter.
• OLD is not accessible in the records of an INSERT.
• NEW is not available on records of a DELETE.

Extra: Creation and use of variables in a TRIGGER

It is important to know that just like in functions and stored procedures, in a TRIGGER we can create variables and manipulate them. Creating a variable is done using the DECLARE command followed by the variable name and its type. For example:

DECLARE $var1 SMALLINT;

And the assignment or modification of its value through the SET command:

SET $var1 = 33;

As you will see I have named the variables with a dollar at the beginning, this is nothing more than my way of differentiating my variable from the names of columns or tables that I use in the TRIGGER. You can create the variables without dollar or even put another character in its place.

For clarity I leave you the last one example TRIGGER modified with use of variables:

DELIMITER $$
CREATE TRIGGER trigger_usuario_identificador
AFTER UPDATE ON usuario
FOR EACH ROW
BEGIN 
   DECLARE $identificador TEXT;
   SET $identificador = CONCAT(NEW.id, '_', NEW.nombre, '_', NEW.dni);
   UPDATE usuario
   SET identificador = $identificador 
   WHERE id = OLD.id;
END; $$