Mysql 简明教程

MySQL - Triggers

通常， Trigger 被定义为对事件的响应。在 MySQL 中，触发器是一个特殊存储过程，驻留在系统目录中，每当执行事件时（无需像常规存储过程那样显式调用），该过程将自动执行（无需像常规存储过程那样显式调用）。这些事件包括诸如 INSERT、UPDATE 和 DELETE 之类的语句。

Generally, a Trigger is defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed automatically (without being called explicitly like regular stored procedures) whenever an event is performed. These events include statements like INSERT, UPDATE and DELETE etc.

根据 SQL 标准，触发器通常分为两类：

As per the SQL standard, triggers are usually divided into two categories −

  1. Row-level Trigger: Triggers that are only executed when each row is either inserted, updated or deleted in a database table. MySQL only supports these type of triggers.

  2. Statement-level Trigger: Triggers like these are executed on the transaction level, once, no matter how many rows are modified in a table. MySQL does not support these trype of triggers.

Types of Triggers in MySQL

MySQL 中有六种类型的行级触发器。它们是：

There are six types of row-level triggers in MySQL. They are:

  1. Before Insert Trigger

  2. After Insert Trigger

  3. Before Update Trigger

  4. After Update Trigger

  5. Before Delete Trigger

  6. After Delete Trigger

Before Insert Trigger

在向表中插入任何值之前执行 Before Insert 触发器。每当执行 INSERT 语句时，Before Insert 触发器就会关闭，然后是插入事务。

The Before Insert Trigger is performed before any value is inserted into the table. Whenever an INSERT statement is executed, the Before Insert trigger goes off, followed by the insertion transaction.

After Insert Trigger

After Insert 触发器与 Before Insert 触发器相反。顾名思义，在将任何值插入表后执行它。每当执行 INSERT 语句时，首先将值插入表中，然后执行触发器。

The After Insert Trigger works opposite to the Before Insert Trigger. As implied by its name, it is performed after any value is inserted into the table. Whenever an INSERT statement is executed, the value is inserted into the table first followed by the execution of the trigger.

Before Update Trigger

在更新或修改表中的任何值之前执行 Before Update 触发器。每当执行 UPDATE 语句时，Before Update 触发器都会关闭，然后是更新事务。

The Before Update Trigger is performed before any value is updated or modified in the table. Whenever an UPDATE statement is executed, the Before Update trigger goes off, followed by the update transaction.

After Update Trigger

After Update 触发器与 Before Update 触发器相反。顾名思义，在表中更新任何值后执行它。每当执行 UPDATE 语句时，首先在表中更新值，然后执行触发器。

The After Update Trigger works opposite to the Before Update Trigger. As implied by its name, it is performed after any value is updated in the table. Whenever an UPDATE statement is executed, the value is updated in the table first followed by the execution of the trigger.

Before Delete Trigger

在从表中删除任何值之前执行 Before Delete 触发器。每当执行 DELETE 语句时，Before Delete 触发器就会关闭，然后是删除事务。

The Before Delete Trigger is performed before any value is deleted from the table. Whenever a DELETE statement is executed, the Before Delete trigger goes off, followed by the deletion transaction.

After Delete Trigger

After Delete 触发器执行与 Before Delete 触发器相反的操作。它的名称暗示了它的功能，它在表中任何值被删除后执行。每当 DELETE 语句被执行，它首先从表中删除值，然后执行该触发器。

The After Delete Trigger works opposite to the Before Delete Trigger. As implied by its name, it is performed after any value is deleted from the table. Whenever an DELETE statement is executed, the value is deleted from the table first followed by the execution of the trigger.

Advantages of Triggers

触发器在 MySQL 数据库中具有很多优点。它们列出如下：

Triggers hold a lot of advantages in MySQL database. They are listed as follows −

  1. Triggers help the database to maintain the integrity of the data stored.

  2. Triggers are also a means to handle errors from the database layer itself.

  3. As triggers are invoked automatically without being called explicitly, you don’t have to wait for the scheduled events to run.

  4. Triggers can be useful to track the data changes made in the tables, by logging the events.

  5. MySQL Triggers can also prevent invalid transactions from being executed.

Disadvantages of Triggers

然而，在 MySQL 数据库中使用触发器也有缺点。其中一些列出如下：

However, there are disadvantages of using triggers in a MySQL database. Some of them are listed as follows −

  1. Triggers cannot replace all validations, and only provide extended validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints.

  2. As triggers are invisible to the client application, it is impossible to understand what goes on in the database layer. Hence, making it difficult to troubleshoot.

  3. Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high.

  4. Triggers may increase the overhead of the MySQL Server.

Restrictions on Triggers

以下是一些适用于 MySQL 触发器的限制：

Following are some of the restrictions that apply to MySQL triggers −

  1. One trigger for each event − Each table can have only one trigger for each event combination, i.e. you can’t define two same triggers for the same table.

  2. RETURN statement is not permitted − As triggers don’t return any values, the RETURN statement is not permitted.

  3. Foreign key restriction − Triggers are not activated by foreign key actions.

  4. Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata.

  5. Cannot use 'CALL' statement − We cannot use the CALL statement in triggers.

  6. Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view.

  7. Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views.