要创建触发器或删除触发器，请使用 CREATE TRIGGER 或 DROP TRIGGER 语句，如 第 15.1.22 节，“CREATE TRIGGER 语句” 和 第 15.1.34 节，“DROP TRIGGER 语句” 所述。To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement, described in Section 15.1.22, “CREATE TRIGGER Statement”, and Section 15.1.34, “DROP TRIGGER Statement”.

这是一个简单的示例，将触发器与表关联，以便在 INSERT 操作时激活。该触发器充当累加器，将插入到表中某一列的值进行求和。Here is a simple example that associates a trigger with a table, to activate for INSERT operations. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

CREATE TRIGGER 语句创建了一个名为 ins_sum 的触发器，该触发器与 account 表相关联。它还包括指定触发器动作时间、触发事件以及触发器激活时执行动作的子句：The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger action time, the triggering event, and what to do when the trigger activates:

要使用触发器，将累加器变量设置为零，执行一个 INSERT 语句，然后查看变量之后的值：To use the trigger, set the accumulator variable to zero, execute an INSERT statement, and then see what value the variable has afterward:

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

在这种情况下，INSERT 语句执行后，@sum 的值为 14.98 + 1937.50 - 100，即 1852.48。In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 - 100, or 1852.48.

要删除触发器，请使用 DROP TRIGGER 语句。如果触发器不在默认模式中，则必须指定模式名称：To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema:

mysql> DROP TRIGGER test.ins_sum;

如果删除一个表，该表的任何触发器也会被删除。If you drop a table, any triggers for the table are also dropped.

触发器名称存在于模式命名空间中，这意味着所有触发器在模式内必须有唯一名称。不同模式中的触发器名称可能相同。Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

可以为给定的表定义多个具有相同触发事件和动作时间的触发器。例如，您可以为一个表设置两个 BEFORE UPDATE 触发器。默认情况下，具有相同触发事件和动作时间的触发器会按照创建顺序激活。要影响触发器的顺序，可以在 FOR EACH ROW 之后指定一个子句，指出 FOLLOWS 或 PRECEDES 以及另一个同样具有相同触发事件和动作时间的已存在触发器的名称。使用 FOLLOWS 时，新触发器在已有触发器之后激活。使用 PRECEDES 时，新触发器在已有触发器之前激活。It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

例如，以下触发器定义为 account 表定义了另一个 BEFORE INSERT 触发器：For example, the following trigger definition defines another BEFORE INSERT trigger for the account table:

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)

这个触发器，ins_transaction，类似于 ins_sum，但它会分别累积存款和取款。它有一个 PRECEDES 子句，使其在 ins_sum 之前激活；如果没有该子句，它会在 ins_sum 之后激活，因为它是在 ins_sum 之后创建的。This trigger, ins_transaction, is similar to ins_sum but accumulates deposits and withdrawals separately. It has a PRECEDES clause that causes it to activate before ins_sum; without that clause, it would activate after ins_sum because it is created after ins_sum.

在触发器体内，OLD 和 NEW 关键字使您能够访问触发器所影响行的列。OLD 和 NEW 是 MySQL 对触发器的扩展；它们不区分大小写。Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

在 INSERT 触发器中，只能使用 NEW.col_name；不存在旧行。在 DELETE 触发器中，只能使用 OLD.col_name；不存在新行。在 UPDATE 触发器中，可以使用 OLD.col_name 来引用更新前行的列，使用 NEW.col_name 来引用更新后行的列。In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

名为 OLD 的列是只读的。你可以引用它（如果你拥有 SELECT 权限），但不能修改它。如果你拥有其 SELECT 权限，你可以引用名为 NEW 的列。在 BEFORE 触发器中，如果你拥有其 UPDATE 权限，你还可以通过 SET NEW.col_name = value 来更改其值。这意味着你可以使用触发器修改要插入新行或用于更新行的值。（在 AFTER 触发器中，这样的 SET 语句没有效果，因为行的更改已经发生。）A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change has already occurred.)

在 BEFORE 触发器中，AUTO_INCREMENT 列的 NEW 值为 0，而不是在新行实际插入时自动生成的序列号。In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the sequence number that is generated automatically when the new row actually is inserted.

通过使用 BEGIN ... END 结构，您可以定义一个执行多个语句的触发器。在 BEGIN 块中，您还可以使用存储例程中允许的其他语法，如条件语句和循环语句。然而，就像存储例程一样，如果您使用 mysql 程序来定义一个执行多个语句的触发器，则有必要重新定义 mysql 语句分隔符，以便您可以在触发器定义中使用 ; 语句分隔符。以下示例说明了这些要点。它定义了一个 UPDATE 触发器，该触发器检查用于更新每一行的新值，并将该值修改为在 0 到 100 的范围内。这必须是一个 BEFORE 触发器，因为必须在使用该值更新行之前进行检查：By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value must be checked before it is used to update the row:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

将存储过程单独定义，然后通过一个简单的 CALL 语句从触发器中调用它，可能会更容易。如果你希望从多个触发器中执行相同的代码，这也很有优势。It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to execute the same code from within several triggers.

触发器激活时，它执行的语句有以下限制：There are limitations on what can appear in statements that a trigger executes when activated:

另请参见 第 27.8 节，“存储程序的限制”。See also Section 27.8, “Restrictions on Stored Programs”.

MySQL 在触发器执行期间的错误处理如下：MySQL handles errors during trigger execution as follows:

触发器可以包含对表的直接名称引用，例如本例中所示的名为 testref 的触发器：Triggers can contain direct references to tables by name, such as the trigger named testref shown in this example:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

假设按如下所示将以下值插入表 test1：Suppose that you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES 
       (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

结果，四个表包含以下数据：As a result, the four tables contain the following data:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)