1. Introduction
In this post, we feature a comprehensive Tutorial on MySQL Triggers and how do they work. MySQL is one of the most used SQL databases in many of the world-enterprise applications due to its ability to manage transactions and the ACID-behaviour which is built into its core. One of the reasons MySQL is so popular is due to the easiness it provides for its usage and its related commands. In the open source web application framework LAMP (which consist of Linux, Apache, MySQL and PHP), MySQL server is a central & important component. The MySQL Database server is written using C and C++ which internally uses a lexical analyser to parse and understand the SQL queries.
For this lesson, we need to have a complete MySQL Server installation so that we can run examples for the Triggers we create. Please go through the MySQL Server Tutorial lesson to understand how installation can be done with some simple commands to get started with MySQL Triggers.
2. What are MySQL Triggers?
MySQL Triggers are simple programs (written in SQL itself) which are executed automatically when something happens. This event can be a simple insertion, update or a delete operation in a Table. Whenever there is an event on a Table for which a Trigger is defined, the program is executed by the MySQL server to do something else as well. This can be any operation which related to keeping the database state consistent or populating a table with a set of events which happened in the update of the table.
MySQL Triggers are a powerful tool which is used at a great extent in maintaining a consistent state of a database and make sure that changes in records of a single table are reflected at all the places needed in the database. Although MySQL triggers sound to be very powerful (they are) but they are also associated with some important limitations which limit their use. The most important limitation with MySQL Triggers is debugging. If you use a MySQL Trigger in an application manage DB consistency, it is difficult to debug any issues because there is no way to apply breakpoints and check the program step by step. Neither logging can be applied to a trigger program. To sum up, let’s cover some great advantages for MySQL Triggers:
- They provide a great way to check and implement data integrity
- Avoid errors in business logic by implementing solutions at the database layer
- They are a great alternative to schedules tasks which can be easily ill-performant. Triggers are triggered at the right moment, after each update operation or as we desire
- Auditing of the database changes is easy to implement with Triggers
Now, it is time to describe the disadvantages to use Triggers in an application:
- If we want to make sure that database integrity and consistency is maintained, Triggers can become quite long and coupled with application logic and still can’t replace all validations which are needed at an application level.
- Triggers work completely behind the scene. For this reason, it is not possible to figure out everything which happens at the database layer.
- If there are too many triggers at the database level, they can block database transactions which are originated from application level until a trigger is complete.
- It is possible that we want to trigger an operation only when an event happens 10 times or 100 times. This is not possible with SQL Triggers as they are stateless in themselves.
3. Getting Started with MySQL Triggers
In MySQL, a Trigger is a very simple tool which consists of SQL statements which are executed when an event occurs. Any table can be associated with a Trigger along with an event. This Trigger can be invoked when the coupled events occur. In MySQL, there is six type of events which are defined here. Also, there are size triggers only, which can be bounded to events in a table. These are:
- BEFORE INSERT is triggered before data is inserted into the associated table.
- AFTER INSERT is triggered after data is inserted into the associated table.
- BEFORE UPDATE is triggered before data in the associated table is updated.
- AFTER UPDATE is triggered after data in the associated table is updated.
- BEFORE DELETE is triggered before data is removed from the associated table.
- AFTER DELETE is triggered after data is removed from the associated table.
Before MySQL v5.7.2, it was not possible to define multiple trigger events for the same trigger event but now it has been made possible. Note that a trigger is only triggered with INSERT, DELETE or UPDATE statements only and not for any other statement like TRUNCATE etc. But again, there are statements which use INSERT statement behind the scenes such as REPLACE statement or LOAD DATA statement. If we use these statements, the corresponding triggers associated with the table will be triggered.
4. Storing MySQL Triggers
MySQL Triggers are stored in the directory /data/classicmodels with the name tablename.TRG and triggername.TRN :
- The
tablename.TRGfile associates the trigger to the corresponding table - The
triggername.TRNfile contains the trigger definition which will be executed once an event occurs
We can back up the MySQL triggers by copying the trigger files to the backup folder or at any other place we wish to. We can also treat the trigger folder as a VCS repository and take its backup whenever something changes.
5. Creating MySQL Triggers
Now that we have some great knowledge related to MySQL triggers and where are they stored, we can start creating a Trigger. Let us look at the basic syntax of a Trigger here:
Trigger Syntax
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END;
Let us look at some points for the syntax we defined above:
CREATE TRIGGERstatement marks the beginning of a Trigger definition. It is good if the Trigger name follows a specific naming convention[trigger time]_[table name]_[trigger event]likeafter_author_added.- A Trigger can be invoked
AFTERorBEFOREan event occurs which must be defined after a name is provided for the Trigger. trigger_eventcan beINSERT,UPDATEorDELETE. Once one of this happens, a Trigger is invoked.- On the second line, we provide the table name with which this Trigger will be associated. This is mandatory to be done as a Trigger without a Table cannot exist in MySQL.
- An SQL Trigger starts with a
BEGINstatement and end with anENDstatement. Between these statements, we define the logic for the Trigger.
Let us start creating an example Trigger. To do this, we first look at the databases we have in our system:
We will make use of jcg_schema schema. Let us look at the tables we have in that schema:
Now, we will create a new table to keep an audit of Authors being added to the system and also when any information about them is edited or they are completely removed from the system. Here is the create table statement for the author_audit table:
Create Author Audit
CREATE TABLE author_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
authorId INT NOT NULL,
name VARCHAR(50) NOT NULL,
changedate DATETIME DEFAULT NULL,
action VARCHAR(50) DEFAULT NULL
);
Now, we create a new Trigger for the Insert event on the AUthor table, like:
Create Insert Trigger
DELIMITER $$
CREATE TRIGGER after_author_added
AFTER INSERT ON Author
FOR EACH ROW
BEGIN
INSERT INTO author_audit
SET action = 'insert',
authorId = NEW.id,
name = NEW.name,
changedate = NOW();
END$$
DELIMITER ;
There is an important thing to notice here. We can get properties of the row being affected through the NEW keyword.
Note that a Trigger made for INSERT uses the NEW keyword whereas the Trigger made for DELETE uses the OLD keyword to access the row being affected. In the UPDATE trigger, OLD can be used to access the row before the change was made to the row and NEW to access the row after the change was made to the row. Let’s make this Trigger and look at all the Triggers which exist in the database as of now with the following command:
Display all triggers
SHOW TRIGGERS;
Let us look at the output for this example:
Here are the columns which are included in the table above:
- Name of the Trigger is displayed
- Event is specified like UPDATE, INSERT or DELETE
- The name of the Table is shown with which the Trigger is associated
- Statement which is being executed is stored in one of the column
- Timing column can contain only two values, BEFORE or AFTER. It specified when is the trigger invoked.
- Created column contains the date time values when this Trigger was created
- sql_mode: This describes the SQL mdoe when the Trigger executes
- Definer provides the account name which created the trigger
To look if our Trigger is working fine, we will now insert an Author into the table and see if contents are updated in the audit table as well, we use the following command:
Insert new Author
INSERT INTO Author (id, name, post_count) VALUES (7, 'Vyom', 27);
Now, we can look at the audit table with the following command:
Audit table
SELECT * FROM author_audit;
Let us look at the output this command shows:
We see that the insertion was done in the audit table as soon as we inserted a new author into the table. Triggers are really fast when you want to perform table manipulation right before or after an operation is done on the table.
6. Deleting the MySQL Triggers
In this final section, we can see how we can delete MySQL Triggers with an SQL command as well. To remove an existing Trigger, we can use the following command:
Audit table
DROP TRIGGER table_name.trigger_name;
For example, if we want to delete the Trigger which we created in the previous section, we can run the following SQL command:
Audit table
DROP TRIGGER Author.after_author_added;
Please note that if we want to modify a Trigger, we will have to delete it first and recreate it again with the new commands. Unfortunately, there is no command like ALTER command for modifying SQL Triggers.
7. Conclusion
In this post, we looked at how we can create triggers for three trigger events in MySQL, including how we can associate them with a table in our database. We also looked at how we can display all the Triggers which exist in our database along with 8 columns in the table which is managed by MySQL itself for administration purposes. One thing to take care when you replicate the database to a new server with only the content from your tables, the Triggers won’t be backed up if you do not take a complete database back up carefully and your database will start to become inconsistent as soon as data manipulation starts in it.
MySQL Triggers are a very powerful tool with very high performance (usually) but the bad thing is that they can grow very large very easily and quickly once your application starts to grow. The main database consistency should be managed at the application level wherever and whenever possible. Still, MySQL Triggers make a very good companion if you are a database administrator and just want to make things consistent without relying on too much code because after all, the performance of a Trigger is very high because database operations are very fast on the database server itself.
We did not cover all the SQL Trigger sections in this lesson but we did provide a very strong foundation on how Triggers can be created and used to manipulate data in a database. Don’t forget to check the examples of using Java with SQL: JDBC Best Practices Tutorial and Java JDBC ResultSet Example. These lessons explain how to effectively use SQL commands with Java & JDBC drivers. There are many more examples present on MySQL which can be studied for a deeper understanding of the database.
