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.
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
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.
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.
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!
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:
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.
We then created a delimiter for the stored procedure.
We then deleted the existing trigger using
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.
Finally, let's assume that we only need to delete a trigger. This is achieved by the code below:
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.