Christian WellsTriggers are a powerful feature of relational databases that allow you to automate actions based on specific events. In the context of Structured Query Language (SQL) and MySQL, triggers can be used to perform actions whenever rows are inserted, updated, or deleted from a table. This guide will provide you with a comprehensive understanding of triggers in MySQL and how to use them effectively.
1. Introduction
When working with relational databases and SQL, most operations on the data are explicitly executed queries such as SELECT, INSERT, or UPDATE. However, triggers allow you to define actions that are automatically performed whenever specific events occur in the database. For example, you can use triggers to keep an audit trail log of all DELETE statements or automatically update aggregated statistical summaries whenever rows are updated or appended to a table.
This tutorial will guide you through the usage of different types of triggers in MySQL, including BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers. By the end of this guide, you will have a solid understanding of how to use triggers to automate actions in your MySQL database.
2. Prerequisites
Before we dive into the details of triggers in MySQL, there are a few prerequisites you need to have in place. First, you will need a computer running a SQL-based relational database management system (RDBMS). In this guide, we will be using MySQL as our RDBMS.
To follow along with the examples in this guide, you will need to have MySQL installed and secured on your server. If you haven’t done so already, you can refer to the official MySQL documentation on how to install MySQL on your specific operating system.
You should also have basic familiarity with executing SELECT, INSERT, UPDATE, and DELETE queries to manipulate data in the database. If you need a refresher on these SQL operations, you can refer to our How To SELECT Rows FROM Tables in SQL, How To Insert Data in SQL, How To Update Data in SQL, and How To Delete Data in SQL guides.
Additionally, it would be helpful to have a basic understanding of nested queries and aggregate mathematical functions. If you’re not familiar with these concepts, you can refer to our How To Use Nested Queries in SQL and How To Use Mathematical Expressions and Aggregate Functions in SQL guides.
3. Connecting to MySQL and Setting up a Sample Database
To follow the examples in this guide, you will need to connect to a MySQL server and create a sample database. We will be using an imaginary collectibles collection as our sample database.
If your MySQL database system runs on a remote server, you can SSH into your server from your local machine. Once connected, open up the MySQL server prompt and create a database named “collectibles” using the following command:
CREATE DATABASE collectibles;
Next, select the “collectibles” database by running the following command:
USE collectibles;
Now that you have selected the database, you can create the sample tables within it. The first table we will create is called “collectibles” and it will contain simplified data about collectibles in the database. It will have two columns: “name” and “value”. The “name” column will hold the name of each collectible, expressed as a varchar with a maximum of 50 characters. The “value” column will store the collectible’s market value using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it. Run the following command to create the “collectibles” table:
CREATE TABLE collectibles ( name varchar(50), value decimal(5, 2));
Next, we will create the “collectiblesstats” table, which will be used to keep track of the accumulated worth of all the collectibles in the collection. This table will have two columns: “count” and “value”. The “count” column will hold the number of owned collectibles, expressed as an int, and the “value” column will store the accumulated worth of all collectibles using the decimal data type. Run the following command to create the “collectiblesstats” table:
CREATE TABLE collectibles_stats ( count int, value decimal(5, 2));
Finally, we will create the “collectiblesarchive” table, which will keep track of all the collectibles that have been removed from the collection. This table will have three columns: “name”, “value”, and “removedon”. The “name” column will hold the name of each removed collectible, the “value” column will store the collectible’s market value at the moment of deletion, and the “removedon” column will store the date and time of deletion for each archived collectible. Run the following command to create the “collectiblesarchive” table:
CREATE TABLE collectibles_archive ( name varchar(50), value decimal(5, 2), removed_on timestamp DEFAULT CURRENT_TIMESTAMP);
Now that you have set up the sample database and tables, you can proceed to the next sections to learn about triggers in MySQL and how to use them effectively.
4. Understanding Triggers
Triggers are statements defined for a particular table that get executed automatically by the database every time a specified event occurs in that table. They can be used to guarantee that some actions will be performed consistently every time a specific statement is executed on a table. Triggers are associated with a table and are identified by a user-defined name and a pair of conditions that determine when the trigger should execute.
There are six possible trigger conditions in MySQL:
- BEFORE INSERT
- BEFORE UPDATE
- BEFORE DELETE
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE
BEFORE triggers execute before the statement that triggered them, while AFTER triggers execute after the statement. This distinction allows you to manipulate data before it gets inserted or updated into the table, or to save details of the deleted rows for auditing or archival purposes. When a trigger is executed, it has access to the data values in the row that triggered it using the OLD and NEW records. The OLD record contains the values before the triggering statement, while the NEW record contains the values that will be saved by the triggering statement.
To create a trigger in MySQL, you use the CREATE TRIGGER statement followed by the trigger name, trigger condition, table name, and the trigger actions. The trigger actions can be a single SQL statement or a block of statements enclosed in BEGIN and END keywords.
Let’s take a closer look at the general syntax of creating a trigger in MySQL:
CREATE TRIGGER trigger_name trigger_condition ON table_name FOR EACH ROW trigger_actions;
trigger_nameis the user-defined name of the trigger.trigger_conditionspecifies when the trigger should execute, such as BEFORE INSERT or AFTER UPDATE.table_nameis the name of the table that the trigger is associated with.trigger_actionsare the actions that the trigger should perform.
In the following sections, we will explore different types of triggers and their use cases in MySQL.
5. Manipulating Data with BEFORE INSERT and BEFORE UPDATE Triggers
BEFORE INSERT and BEFORE UPDATE triggers allow you to manipulate data before it gets inserted or updated into a table. In this section, we will explore how to use these triggers to ensure data consistency and integrity.
5.1 Ensuring Uppercase Names with BEFORE INSERT Trigger
Let’s say you want to ensure that all collectible names in the “collectibles” table are stored in uppercase letters for consistency. Without triggers, you would need to remember to use uppercase names in every INSERT and UPDATE statement. However, with a BEFORE INSERT trigger, you can automate the process of converting the names to uppercase.
To create a BEFORE INSERT trigger for the “collectibles” table, use the following command:
CREATE TRIGGER uppercase_before_insert BEFORE INSERT ON collectibles FOR EACH ROW SET NEW.name = UPPER(NEW.name);
In this trigger, the SET statement assigns the uppercase version of the collectible name to the NEW.name column. The NEW record represents the data that will be saved by the triggering INSERT statement. By applying the UPPER function to the collectible name, you ensure that it is stored in uppercase.
Now, let’s test the trigger by inserting a collectible with a lowercase name:
INSERT INTO collectibles VALUES ('spaceship model', 12.50);
When you retrieve the data from the “collectibles” table, you will see that the name has been automatically converted to uppercase:
SELECT * FROM collectibles;
Output:
+-----------------+-------+ | name | value | +-----------------+-------+ | SPACESHIP MODEL | 12.50 | +-----------------+-------+
The BEFORE INSERT trigger successfully converted the lowercase name to uppercase, ensuring consistency in the data.
5.2 Updating Uppercase Names with BEFORE UPDATE Trigger
In addition to ensuring consistent uppercase names during the insertion of new collectibles, you may also want to enforce this consistency when updating existing collectibles. You can achieve this by using a BEFORE UPDATE trigger.
Let’s create a BEFORE UPDATE trigger for the “collectibles” table:
CREATE TRIGGER uppercase_before_update BEFORE UPDATE ON collectibles FOR EACH ROW SET NEW.name = UPPER(NEW.name);
This trigger is similar to the BEFORE INSERT trigger, but it will execute before any UPDATE statement on the “collectibles” table. It will convert the name to uppercase before the update is applied.
Now, let’s test the trigger by updating the value of a collectible:
UPDATE collectibles SET value = 15.00 WHERE name = 'SPACESHIP MODEL';
When you retrieve the data from the “collectibles” table, you will see that the updated value is preserved and the name remains in uppercase:
SELECT * FROM collectibles;
Output:
+-----------------+-------+ | name | value | +-----------------+-------+ | SPACESHIP MODEL | 15.00.| +-----------------+-------+
The BEFORE UPDATE trigger successfully maintained the uppercase name while updating the value.
By using BEFORE INSERT and BEFORE UPDATE triggers, you can ensure data consistency and integrity by automatically manipulating the data before it is inserted or updated in the table.
6. Using BEFORE DELETE Triggers to Execute Actions Before Deleting Rows
BEFORE DELETE triggers allow you to execute actions before rows are deleted from a table. In this section, we will explore how to use BEFORE DELETE triggers to archive deleted rows in a separate table.
6.1 Archiving Deleted Rows with a BEFORE DELETE Trigger
Let’s say you want to keep track of all the collectibles that have been removed from the collection by archiving them in a separate table called “collectibles_archive”. You can achieve this by creating a BEFORE DELETE trigger.
To create a BEFORE DELETE trigger for the “collectibles” table, use the following command:
CREATE TRIGGER archive_before_delete BEFORE DELETE ON collectibles FOR EACH ROW INSERT INTO collectibles_archive (name, value, removed_on) VALUES (OLD.name, OLD.value, NOW());
In this trigger, the INSERT statement inserts a new row into the “collectiblesarchive” table with the values from the deleted row. The OLD record represents the data in the row that will be deleted. The trigger also includes the NOW() function to store the current date and time in the “removedon” column.
Now, let’s test the trigger by deleting a collectible from the “collectibles” table:
DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';
When you retrieve the data from the “collectibles” table, you will see that the deleted row is no longer present:
SELECT * FROM collectibles;
Output:
+-----------------+-------+ | name | value | +-----------------+-------+ | AIRCRAFT MODEL | 10.00.| +-----------------+-------+
If you retrieve the data from the “collectibles_archive” table, you will see that the deleted row has been archived:
SELECT * FROM collectibles_archive;
Output:
+-----------------+-------+---------------------+ | name | value | removed_on | +-----------------+-------+---------------------+ | SPACESHIP MODEL | 15.00 | 2022-01-0100:00:00 | +-----------------+-------+---------------------+
The BEFORE DELETE trigger successfully archived the deleted row in the “collectibles_archive” table, preserving the name, value, and the date and time of deletion.
By using BEFORE DELETE triggers, you can perform actions before deleting rows, such as archiving or auditing deleted data.
7. Using AFTER INSERT, AFTER UPDATE, and AFTER DELETE Triggers to Execute Actions After Data Manipulation
AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers allow you to execute actions after rows have been inserted, updated, or deleted from a table. In this section, we will explore how to use these triggers to update a summary table with aggregated values based on all the collectibles in the collection.
7.1 Updating a Summary Table with an AFTER INSERT Trigger
Let’s say you want to maintain a summary table called “collectibles_stats” that contains the count of owned collectibles and the accumulated worth of all collectibles. You can achieve this by creating an AFTER INSERT trigger.
To create an AFTER INSERT trigger for the “collectibles” table, use the following command:
CREATE TRIGGER stats_after_insert AFTER INSERT ON collectibles FOR EACH ROW UPDATE collectibles_stats SET count = (SELECT COUNT(*) FROM collectibles), value = (SELECT SUM(value) FROM collectibles);
In this trigger, the UPDATE statement updates the “collectibles_stats” table with the count of collectibles and the sum of their values. The subqueries in the SET clause calculate these values by querying the “collectibles” table.
Now, let’s test the trigger by inserting a new collectible into the “collectibles” table:
INSERT INTO collectibles VALUES ('ship model', 10.00);
When you retrieve the data from the “collectibles_stats” table, you will see that the summary has been updated with the new collectible:
SELECT * FROM collectibles_stats;
Output:
+-------+-------+ | count | value | +-------+-------+ | 2 | 22.50 | +-------+-------+
The AFTER INSERT trigger successfully updated the summary table with the correct count and value.
7.2 Updating a Summary Table with AFTER UPDATE and AFTER DELETE Triggers
In addition to updating the summary table after an insert, you may also want to update it after an update or delete operation. You can achieve this by creating separate AFTER UPDATE and AFTER DELETE triggers.
To create an AFTER UPDATE trigger for the “collectibles” table, use the following command:
CREATE TRIGGER stats_after_update AFTER UPDATE ON collectibles FOR EACH ROW UPDATE collectibles_stats SET count = (SELECT COUNT(*) FROM collectibles), value = (SELECT SUM(value) FROM collectibles);
This trigger is similar to the AFTER INSERT trigger, but it will execute after any UPDATE statement on the “collectibles” table.
To create an AFTER DELETE trigger for the “collectibles” table, use the following command:
CREATE TRIGGER stats_after_delete AFTER DELETE ON collectibles FOR EACH ROW UPDATE collectibles_stats SET count = (SELECT COUNT(*) FROM collectibles), value = (SELECT SUM(value) FROM collectibles);
This trigger is similar to the AFTER INSERT and AFTER UPDATE triggers, but it will execute after any DELETE statement on the “collectibles” table.
Now, let’s test the triggers by updating the value of a collectible and deleting a collectible from the “collectibles” table:
UPDATE collectibles SET value = 25.00 WHERE name = 'ship model'; DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';
When you retrieve the data from the “collectibles_stats” table, you will see that the summary has been updated to reflect the changes:
SELECT * FROM collectibles_stats;
Output:
+-------+-------+ | count | value | +-------+-------+ | 1 | 25.00 | +-------+-------+
The AFTER UPDATE and AFTER DELETE triggers successfully updated the summary table with the correct count and value.
By using AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers, you can keep a summary table up to date with aggregated values based on all the collectibles in the collection.
8. Listing and Deleting Triggers
In addition to creating triggers, you can also list and delete existing triggers in MySQL. This can be useful when you want to manage the triggers associated with a table.
To list all triggers in a database, you can use the SHOW TRIGGERS statement:
SHOW TRIGGERS;
This command will display a list of all triggers, including their names, events, tables, statements, and timings.
To delete a trigger, you can use the DROP TRIGGER statement followed by the trigger name:
DROP TRIGGER trigger_name;
This command will remove the specified trigger from the database.
9. Conclusion
Triggers are a powerful feature in MySQL that allow you to automate actions based on specific events in a database. In this comprehensive guide, we explored different types of triggers and their use cases in MySQL.
We started by understanding the basics of triggers and their syntax. Then, we looked at how to use BEFORE INSERT and BEFORE UPDATE triggers to manipulate data before insertion or update. We also explored how to use a BEFORE DELETE trigger to archive deleted rows in a separate table.
Next, we delved into the usage of AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers to perform actions after data manipulation. We learned how to update a summary table with aggregated values based on all the collectibles in the collection.
Finally, we discussed how to list and delete triggers in MySQL.
Triggers are a powerful tool that can help you maintain data consistency, integrity, and automate actions in your MySQL databases. By leveraging triggers effectively, you can streamline your database operations and improve overall efficiency.
If you’re looking for a reliable and scalable cloud hosting solution for your MySQL databases, consider Shape.host’s Cloud VPS services. With Shape.host, you can enjoy high-performance virtual private servers, robust security measures, and exceptional customer support. Visit Shape.host for more information and take your MySQL databases to the next level.
