Triggers

Sometimes it may be necessary to respond to events in our database. There may be various situations, for example:

• When deleting a row from the database, we would like to save it somewhere;

• Or when updating a row, we need to check that the new incoming data is not empty;

• And finally, when creating a new record, we need to make sure that the incoming data is valid;

• And not only that...

Now, we will get acquainted with the tool that runs our commands, when any of the following statements come in: INSERT , UPDATE, or DELETE.

What are triggers?

A trigger is a database object that is associated with the database table. Accordingly, when you delete a table, all triggers associated with it will also be deleted.

A trigger is a stored procedure that you create and specifically place inside your table. After that, it will independently execute the created operations inside the database. It will be automatically activated when the event defined in your trigger arrives at the database table.

Triggers have the following advantages. As they are self-executing from within the database, they are often used to save incoming information to multiple database locations. Also, they may be recording and logging user actions, and checking rules for incoming data into database tables.

Because triggers run on their own, you need to keep track of what triggers are in your database and what they do, as it's almost impossible to track their actions from the outside.

Create trigger

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name. The user must have the TRIGGER privilege value for the table on which they create an associated trigger.

For example, let's create a simple table named employee with id , name and salary columns and fill it with some data:

Great! Our company has grown and now we need to know, how much the salary budget will increase if we hire new employees. To do this, in the employee table, CREATE new TRIGGER additional_salary, that will be executed BEFORE INSERT a new employee into the table, each time we will increase the new variable @sum.

CREATE TRIGGER additional_salary
BEFORE INSERT ON employee 
FOR EACH ROW 
SET @sum = @sum + NEW.salary;

Let's see how it works.

First, let's add new employees, but before that, we will reset the variable @sum:

SET @sum = 0;

After that:

INSERT INTO employee VALUES(4, 'Alex', 800.00),(5, 'Julia', 1937.50),(6, 'Andrew', 1000.00);

Our employee table now looks like this:

At first glance, nothing happened and no warnings or additional messages appeared. Let's check our variable @sum:

SELECT @sum AS 'Total additional salary';

And we get the following result:

It works! Here we see that variable now has the total sum salary of the last three employees.

More triggers

After we learned how to create triggers, we will probably want to create more triggers. We can create two separate triggers for one action, that would activate BEFORE or AFTER the trigger event. But what if we want to create more? No problem!

By default, triggers that have the same trigger event and action time are activated in the very order they were created. To change the activation order of such triggers, you must specify a new FOLLOWS or PRECEDES modifier and name of existing trigger.

Let's CREATE new TRIGGER big_salary in the employee table. Each time,BEFORE INSERT a new employee into the table, it will check that the employee's salary does not exceed the given amount, and IF it exceeds, abort the operation and show an error message:

CREATE TRIGGER big_salary
BEFORE INSERT ON employee
FOR EACH ROW PRECEDES additional_salary
IF NEW.salary>5000 THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary has exceeded the allowed amount of 5000.00';
END IF;

This trigger big_salary has a PRECEDES modificator, that causes it to activate before additional_salary. Without that modificator, it would activate after additional_salary because it was created later. You can also use the FOLLOWS modificator, if you want to place a new trigger after the existing trigger in the table.

Let's try to INSERT a new employee whose salary is very big:

INSERT INTO employee VALUES(7, 'Mark', 8000.00);

A new trigger will immediately be activated and give an error message:

[45000][1644] Salary has exceeded the allowed amount of 5000.00

After that, we can make sure that the trigger additional_salary is not activated and the @sum variable remained unchanged:

SELECT @sum AS 'Total additional salary';

Nothing has changed, so everything works as it should!

Multiple statements

By using the BEGIN ... END construct, you can define a trigger that executes multiple statements.

The following example illustrates how to CREATE new UPDATE trigger upd_check_salary that checks the new employee.salary for updating each row, and modifies the value to be within the range from 0 to 5000.00. This must be a BEFORE action, because the value must be checked before it is used to update the row:

CREATE TRIGGER upd_check_salary
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    ELSEIF NEW.salary > 5000.00 THEN
        SET NEW.salary = 5000.00;
    END IF;
END;

How does it work? Try to update the salary of employee Alex:

UPDATE employee SET salary = 6000 WHERE name = 'Alex';

Now let's check Alex's salary:

SELECT name, salary FROM employee WHERE name = 'Alex';

We get the following result:

Despite the fact that we wanted to give Alex a salary of 6000, this trigger activated in the table and corrected it to 5000.

Save deleted data

Before we make the next step, let's CREATE a new TABLE fired_employee, where we will put the data that someone tries to delete from the employee table.

CREATE TABLE fired_employee (ID INT, name VARCHAR(20), fire_date DATE);

Now everything is ready to CREATE new TRIGGER save_fire_employee in the table employee, that AFTER DELETE employee will INSERT data into our new fired_employee table.

CREATE TRIGGER save_fire_employee
    AFTER DELETE
    ON employee
    FOR EACH ROW
BEGIN
    INSERT INTO fired_employee VALUES (OLD.id, OLD.name, CURDATE());
    SET @sum = @sum - OLD.salary;
END;

Pay attention to the OLD modifier, since the DELETE command only tells what needs to be deleted and does not have additional data. We have the opportunity to operate the OLD data that was in the table. The NEW modifier is used to access data that comes into the database table with the following trigger events: INSERT and UPDATE.

And try to DELETE FROM employee Benedict with id 1:

DELETE FROM employee WHERE id = 1;

Now, our employee table looks like this:

We don't see Benedict, but now we have him in this fired_employee table:

And check the @sum variable:

Everything is working!

Show triggers

Now that we're done creating triggers, it's time to take a look at our results:

SHOW TRIGGERS;

We get the following table, quite a long one:

Every column contains the following attributes:

• Trigger is the name of the trigger.

• Event shows the type of operation on the associated table for which the trigger activates.

• Table is the name of the table where the trigger is defined.

• Statement shows what the trigger does.

• Timing shows whether the trigger activates before or after the triggering event.

• Created prints the date and time when a user made the trigger.

• sql_mode lists the SQL server modes in effect when the trigger executes.

• Definer specifies the user and connection who created the trigger.

• character_set_client shows the character set for statements that come from the client.

• collation_connection defines the character set rules for comparing and sorting strings when the definer created the trigger.

• Database Collation shows the character set for the database when sorting and comparing strings associated with the trigger.

Drop trigger

Now we can DROP any TRIGGER. For example, try to delete big_salary:

DROP TRIGGER big_salary;

Now let's check the triggers:

SHOW TRIGGERS;

We successfully removed it!

Conclusion

Now we know how to create a trigger. Remember that triggers are always associated with the table we create them for.

• Triggers only respond to incoming in table events: INSERT , UPDATE , DELETE;

• And they can be triggered both before the event, and after: BEFORE , AFTER;

• We can also create several triggers for one event and set up the activation sequence using modifiers: FOLLOWS , PRECEDES;

• And finally, we can look at all the triggers in our database: SHOW TRIGGERS FROM And remove it: DROP TRIGGER.

Now it's time to practice creating triggers!