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\u00a0the MySQL Server Tutorial<\/a> lesson to understand how installation can be done with some simple commands to get started with MySQL Triggers.<\/p>\n 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.<\/p>\n 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\u00a0limitations<\/strong> 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\u00a0advantages<\/strong> for MySQL Triggers:<\/p>\n Now, it is time to describe the\u00a0disadvantages<\/strong> to use Triggers in an application:<\/p>\n 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:<\/p>\n 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\u00a0only triggered with\u00a0INSERT, DELETE or UPDATE statements only<\/strong> and not for any other statement like TRUNCATE etc. But again, there are statements which use\u00a0INSERT 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.<\/p>\n MySQL Triggers are stored in the directory 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.<\/p>\n 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:<\/p>\n Trigger Syntax<\/em><\/span><\/p>\n Let us look at some points for the syntax we defined above:<\/p>\n Let us start creating an example Trigger. To do this, we first look at the databases we have in our system:<\/p>\n We will make use of 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 Create Author Audit<\/em><\/span><\/p>\n Now, we create a new Trigger for the Insert event on the AUthor table, like:<\/p>\n Create Insert Trigger<\/em><\/span><\/p>\n There is an important thing to notice here. We can get properties of the row being affected through the NEW keyword.<\/p>\n Note that a Trigger made for INSERT uses the Display all triggers<\/em><\/span><\/p>\n Let us look at the output for this example:<\/p>\n Here are the columns which are included in the table above:<\/p>\n 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:<\/p>\n Insert new Author<\/em><\/span><\/p>\n Now, we can look at the audit table with the following command:<\/p>\n Audit table<\/em><\/span><\/p>\n Let us look at the output this command shows:<\/p>\n 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.<\/p>\n 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:<\/p>\n Audit table<\/em><\/span><\/p>\n For example, if we want to delete the Trigger which we created in the previous section, we can run the following SQL command:<\/p>\n Audit table<\/em><\/span><\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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\u2019t forget to check the examples of using Java with\u00a0SQL: JDBC Best Practices Tutorial<\/a>\u00a0and\u00a0Java JDBC ResultSet Example<\/a>. These lessons explain how to effectively use SQL commands with Java &\u00a0JDBC drivers<\/a>. There are many more examples present on MySQL which can be studied for a deeper understanding of the database.<\/p>\n","protected":false},"excerpt":{"rendered":" 1. Introduction In this post, we feature a comprehensive Tutorial on MySQL Triggers and how do they work.\u00a0MySQL\u00a0is 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 …<\/p>\n","protected":false},"author":154,"featured_media":1204,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53],"tags":[647,1735],"class_list":["post-61006","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-mysql","tag-triggers"],"yoast_head":"\n2. What are MySQL Triggers?<\/h2>\n
\n
\n
3. Getting Started with MySQL Triggers<\/h2>\n
\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/10\/mysql-trigger.png\")
<\/a>4. Storing MySQL Triggers<\/h2>\n
\/data\/classicmodels<\/code> with the name\u00a0tablename.TRG<\/code> and triggername.TRN<\/code> :<\/p>\n\n
tablename.TRG<\/code> file associates the trigger to the corresponding table<\/li>\ntriggername.TRN<\/code> file contains the trigger definition which will be executed once an event occurs<\/li>\n<\/ol>\n5. Creating MySQL Triggers<\/h2>\n
CREATE TRIGGER trigger_name trigger_time trigger_event\r\n ON table_name\r\n FOR EACH ROW\r\n BEGIN\r\n ...\r\n END;\r\n<\/pre>\n
\n
CREATE TRIGGER<\/code> statement 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]<\/code> like after_author_added<\/code>.<\/li>\nAFTER<\/code> or BEFORE<\/code> an event occurs which must be defined after a name is provided for the Trigger.<\/li>\ntrigger_event<\/code> can be INSERT<\/code>, UPDATE<\/code> or DELETE<\/code>. Once one of this happens, a Trigger is invoked.<\/li>\nBEGIN<\/code> statement and end with an END<\/code> statement. Between these statements, we define the logic for the Trigger.<\/li>\n<\/ul>\n![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/09\/show-databases.png\")
<\/a>jcg_schema<\/code> schema. Let us look at the tables we have in that schema:<\/p>\n![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/10\/mysql-table-list.png\")
<\/a>author_audit<\/code> table:<\/p>\nCREATE TABLE author_audit (\r\n id INT AUTO_INCREMENT PRIMARY KEY,\r\n authorId INT NOT NULL,\r\n name VARCHAR(50) NOT NULL,\r\n changedate DATETIME DEFAULT NULL,\r\n action VARCHAR(50) DEFAULT NULL\r\n);\r\n<\/pre>\n
DELIMITER $$\r\nCREATE TRIGGER after_author_added\r\n AFTER INSERT ON Author\r\n FOR EACH ROW\r\nBEGIN\r\n INSERT INTO author_audit\r\n SET action = 'insert',\r\n authorId = NEW.id,\r\n name = NEW.name,\r\n changedate = NOW();\r\nEND$$\r\nDELIMITER ;\r\n<\/pre>\n
NEW<\/code> keyword whereas the Trigger made for DELETE uses the OLD<\/code> keyword to access the row being affected. In the UPDATE trigger, OLD<\/code> can be used to access the row before the change was made to the row and NEW<\/code> 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:<\/p>\nSHOW TRIGGERS;\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/10\/mysql-trigger-display.png\")
<\/a>\n
INSERT INTO Author (id, name, post_count) VALUES (7, 'Vyom', 27);\r\n<\/pre>\n
SELECT * FROM author_audit;\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/10\/mysql-trigger-audit.png\")
<\/a>6. Deleting the MySQL Triggers<\/h2>\n
DROP TRIGGER table_name.trigger_name;\r\n<\/pre>\n
DROP TRIGGER Author.after_author_added;\r\n<\/pre>\n
7. Conclusion<\/h2>\n