Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the brief introduction of mysql trigger, the creation of trigger and the limitation of use. Share with you for your reference, the details are as follows:

Brief introduction

A SQL trigger is a set of SQL statements that are stored in the database directory. Whenever an event associated with a table occurs, an SQL trigger, such as insert, update, or delete, is executed or triggered. 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 when a data modification event is performed on a table, the trigger is automatically called, while the stored procedure must be explicitly called.

When we're done, let's take a look at the advantages of SQL triggers:

SQL triggers provide an alternative to checking data integrity. SQL triggers can catch errors in the 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 the scheduled task to run, because triggers are automatically invoked before or after making changes to the data in the table. SQL triggers are useful for auditing changes to data in a table.

Let's take a look at its shortcomings:

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

Now that triggers are special stored procedures, how do we choose between them? As suggested below, if we cannot use stored procedures to do our work, we can consider using SQL triggers.

Create trigger

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

BEFORE INSERT-the trigger is activated before the data is inserted into the table. AFTER INSERT-activate the trigger after the data has been inserted 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-activate the trigger after the data is deleted from the table.

However, starting with MySQL version 5.7.2 +, multiple triggers can be defined for the same trigger event and action time. When you use INSERT,DELETE or UPDATE statements to change the statement of data in a table, the trigger associated with the table is not called. 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 trigger associated with the table is called. So we have to use a unique name for each trigger associated with the table. It is a good practice to define the same trigger name for different tables. Let's take a look at the syntax structure that defines the trigger:

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

For example, before_order_update is the trigger called before updating the row data in the orders table. 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 above.

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 trigger definitions.

So we can back up the mysql trigger by copying the trigger file to the backup folder, or we can back up the trigger using the mysqldump tool.

Use restriction

Mysql triggers cover all the features defined in the standard SQL, but there are some limitations to using them in applications:

Used on top of 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 prepare statements, such as PREPARE,EXECUTE, etc., use dynamic SQL statements.

All right, that's all for this record.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.