What is a Trigger?

A trigger is a named database object associated with a table that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values inserted into a table or to perform calculations on values involved in an update.

Trigger Events

A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements and an insert trigger activates for each inserted row.

A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activated before each row is inserted into a table or after each updated row. More data about triggers can be found in the MySQL 8.0 Reference Manual.

ℹ️
This article intends to teach a way to play with MySQL triggers for a specific use case provided by the scenario. I assume that you already have basic knowledge of triggers and MySQL queries to proceed. Else you can look them up here. The scenario has no defined structure for mentioned tables; it's up to you on how to use them.

Scenario

Let's say we have two tables on a game's API server, user and stats. The table 'user' consist of users' basic information. The table 'stats' consists of user statistics. A user has multiple stats values (one to many relationships) based on game modes: classic and adventure (for now).

Creation of Trigger

Let's set a condition that we need to create related stats data for every new user inserted in the stats table for a given game mode.

For this, we will create a MySQL trigger on user creation with the code below.

CREATE TRIGGER `on_user_create` AFTER INSERT ON `user` FOR EACH ROW
BEGIN
    INSERT INTO stats(user_id, mode) VALUES (NEW.id, 'classic');
    INSERT INTO stats(user_id, mode) VALUES (NEW.id, 'adventure');
END;
Create Trigger Sample Code for Mysql in the Given Scenario

The code above is a trigger named on_user_create that executes after insertion on the user table. For each inserted row, the trigger executes an insert into the stats table as defined by the queries between BEGIN and END tags.

Now, if we create a new user in the user table, it will automatically create two rows in stats for that user with game modes classic and adventure. Neat!

Updating Triggers

Now, let's say we need to update the trigger because we need to insert statistics for a new game mode called league.

We can use an updating trigger as: 

lock tables `user` write ;
delimiter $$
drop trigger if exists `on_user_create`;
$$
CREATE TRIGGER `on_user_create` AFTER INSERT ON `users` FOR EACH ROW
BEGIN
    INSERT INTO stats(user_id, mode) VALUES (NEW.id, 'classic');
    INSERT INTO stats(user_id, mode) VALUES (NEW.id, 'adventure');
    INSERT INTO stats(user_id, mode) VALUES (NEW.id, 'league');
END;
$$
delimiter ;
unlock table ;
Updating Trigger Sample Code for Mysql in the Given Scenario

Let's examine the code above.

First of all, we locked the table users for a write. A lock is a flag associated with a table. MySQL allows a client session to explicitly acquire a table lock to prevent other sessions from accessing the same table during a specific period.

ℹ️
For more information about table locking, check out this link.

We then created a delimiter for the stored procedure.

ℹ️
You can read the SQL docs here and here for more information about using delimiters.

We then deleted the existing trigger using drop trigger.

Finally, we created a new trigger similar to the trigger we created before, but this time with a new added insert query for the new game mode, league.

If we now create a new user in the user table, it will automatically create three rows in the stats table for that user with game modes classic, adventure and league.

Deleting Trigger

Finally, let's assume that we only need to delete a trigger. This is achieved by the code below:

drop trigger if exists `on_user_create`;
Deleting Trigger Sample Code for Mysql in the Given Scenario

This just deletes the trigger if found. If we create a new user in the user table, it will no longer automatically create rows in the stats table.

I hope you found this useful! The article was written with a specific scenario presented.

If you have queries, fill up the comment section and don't forget to subscribe using the buttons below.