Introduction to MySQL triggers, creation of triggers and analysis of usage restrictions

This article uses examples to describe the introduction to MySQL triggers, how to create triggers, and their usage restrictions. Share with you for your reference, the details are as follows:

Introduction

A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs, such as an insert, update, or delete. SQL triggers can also be considered a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between triggers and stored procedures is that triggers are automatically called when data modification events are performed on a table, while stored procedures must be called explicitly.

Now let's look at the advantages of SQL triggers:

• SQL triggers provide an alternative method of checking data integrity.
• SQL triggers can catch errors in business logic in the database layer.
• SQL triggers provide another way to run scheduled tasks. By using SQL triggers, you do not have to wait for a scheduled task to run because the trigger is automatically called before or after a change is made to the data in the table.
• SQL triggers are very useful for auditing changes to data in tables.

Let's look at its shortcomings:

• SQL triggers can only provide extended validation and cannot replace all validation. Some simple validation must be done at the application layer. For example, you can validate user input on the client side using JavaScript or on the server side using a server-side scripting language such as JSP, PHP, ASP.NET, Perl, etc.
• The invocation and execution of SQL triggers from the client application is not visible, so it is difficult to figure out what is happening in the database layer.
• SQL triggers can add overhead to the database server.

Since triggers are a special type of stored procedure, how do we choose between them? The following is just a suggestion. If we cannot use stored procedures to complete the work, we can consider using SQL triggers.

Create a trigger

In MySQL, a trigger is a set of SQL statements that are automatically called when changes are made to data on the associated tables. Triggers can be defined to be invoked before or after an insert, update, or delete statement changes data. Prior to MySQL 5.7.2, a maximum of six triggers could be defined per table. Let's take a look at their brief introduction:

• BEFORE INSERT − The trigger is activated before data is inserted into the table.
• AFTER INSERT − Activates the trigger after data is inserted into the table.
• BEFORE UPDATE − Activates the trigger before the data in the table is updated.
• AFTER UPDATE − Activates the trigger after data in the table is updated.
• BEFORE DELETE − The trigger is activated before data is deleted from the table.
• AFTER DELETE − The trigger is activated after data is deleted from the table.

However, starting with MySQL 5.7.2+, you can define multiple triggers for the same trigger event and action time. When you use statements that change data in a table without using INSERT, DELETE, or UPDATE statements, the triggers associated with the table are not called. For example, the truncate statement deletes all data in a table but does not invoke triggers associated with the table. However, some statements use the background INSERT statement, such as the REPLACE statement or the LOAD DATA statement. If these statements are used, the corresponding triggers associated with the table are called. So we have to use a unique name for each trigger associated with the table. It is good practice to define the same trigger name for different tables. Let's look at the syntax structure for defining a trigger:

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

For example, before_order_update is a trigger that is called before a row in the orders table is updated. Let's look at another way of defining it:

tablename_(BEFORE | AFTER)_(INSERT | UPDATE | DELETE)

For example, order_before_update is the same as the before_order_update trigger described above.

MySQL stores triggers in the data directory, for example: /data/luyaran/, and uses files named tablename.TRG and triggername.TRN:

• The tablename.TRG file maps the trigger to the corresponding table.
• The triggername.TRN file contains the trigger definition.

So we can back up mysql triggers by copying the trigger files to the backup folder, and we can also use the mysqldump tool to back up the triggers.

Limitation of Use

MySQL triggers cover all the functionality defined in standard SQL, however, there are some limitations on using them in your applications:

• Used in SHOW, LOAD DATA, LOAD TABLE, BACKUP DATABASE, RESTORE, FLUSH, and RETURN statements.
• Use statements that implicitly or explicitly commit or rollback, such as COMMIT, ROLLBACK, START TRANSACTION, LOCK/UNLOCK TABLES, ALTER, CREATE, DROP, RENAME, etc.
• Use prepared statements such as PREPARE, EXECUTE, etc.
• Use dynamic SQL statements.

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.