Introduction to mysql Triggers Trigger Creation and Analysis of Usage Restrictions

This article describes the introduction of mysql triggers, the creation of triggers and the restrictions on their use. Share it for your reference, as follows:

Brief introduction

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

Let's look at the advantages of SQL triggers:

SQL triggers provide an alternative way to check data integrity. SQL triggers catch errors in the business logic in the database tier. SQL triggers provide another way to run scheduled tasks. By using SQL triggers, you don't have to wait for scheduled tasks to run, because triggers are automatically invoked before or after changes are made to the data in the table. SQL triggers are useful for auditing changes to data in tables.

Let's look at its shortcomings again:

SQL triggers can only provide extended authentication and cannot replace all authentication. 1 Some simple validation must be completed in the application layer. For example, you can use JavaScript or server-side scripting languages (such as JSP, PHP, ASP. NET, Perl, etc.) to validate user input on the client. Calling and executing SQL triggers from the client application is not visible, so it is difficult to figure out what is happening in the database tier. SQL triggers may increase the overhead of the database server.

Since triggers are a special kind of stored procedure, how do we choose between them? If we can't use stored procedures to get things done, consider using the following SQL triggers.

Create Triggers

In MySQL, a trigger is a set of SQL statements that are automatically invoked when changes are made to the data on the associated table. Triggers can be defined to be invoked before or after an insert, update, or delete statement changes data. Before MySQL version 5.7. 2, each table could define up to 6 triggers. Let's take a look at a brief introduction to them:

BEFORE INSERT-Triggers are activated before data is inserted into the table. AFTER INSERT-Activates the trigger after inserting data into the table. BEFORE UPDATE-Activates the trigger before the data in the table is updated. AFTER UPDATE-Activates the trigger after the data in the table is updated. BEFORE DELETE-Activates the trigger before deleting data from the table. AFTER DELETE-Activates the trigger after deleting data from the table.

However, starting with MySQL 5.7. 2 +, multiple triggers can be defined for the same trigger event and action time. The trigger associated with the table is not invoked when you use a statement that does not use an INSERT, DELETE, or UPDATE statement to change the data in the table. For example, the truncate statement deletes all data from a table, but does not call the trigger associated with the table. However, some statements use background INSERT statements, such as REPLACE statements or LOAD DATA statements. If these statements are used, the corresponding triggers associated with the table are called. Therefore, we must use the only 1 name for each trigger associated with the table. It is a good practice to define the same trigger name for different tables. Let's look at the syntax structure that defines triggers:

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

For example, before_order_update is a trigger that is called before updating the row data in the orders table. Let's look at another definition:

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

For example, order_before_update is the same as the above before_order_update flip-flop.

mysql stores triggers in the data directory, such as:/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 the mysql triggers by copying the trigger files to the backup folder, and we can also back up the triggers using the mysqldump tool.

Use restriction

mysql triggers cover all of the functionality defined in the standard SQL, but there are one limitation to their use in applications:

Used on SHOW, LOAD DATA, LOAD TABLE, BACKUP DATABASE, RESTORE, FLUSH, and RETURN statements. Use implicit or explicit commit or rollback statements, such as COMMIT, ROLLBACK, START TRANSACTION, LOCK/UNLOCK TABLES, ALTER, CREATE, DROP, RENAME, etc. Use preparation statements, such as PREPARE, EXECUTE, and so on Use dynamic SQL statements.

Ok, that's all for this record.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Encyclopedia" and "MySQL Common Functions Encyclopedia"

I hope this article is helpful to everyone's MySQL database.