13.1.22 CREATE TRIGGER Statement语句

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

This statement creates a new trigger. 此语句创建一个新触发器。A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. 触发器是与表关联的命名数据库对象，当表发生特定事件时激活。The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. 触发器与名为tbl_name的表关联，该表必须引用永久表。You cannot associate a trigger with a TEMPORARY table or a view.不能将触发器与临时表或视图关联。

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.不同架构中的触发器可以具有相同的名称。

This section describes CREATE TRIGGER syntax. 本节介绍CREATE TRIGGER语法。For additional discussion, see Section 25.3.1, “Trigger Syntax and Examples”.有关更多讨论，请参阅第25.3.1节，“触发器语法和示例”。

CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. CREATE TRIGGER要求与触发器关联的表具有TRIGGER权限。If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 25.6, “Stored Object Access Control”. 如果存在DEFINER子句，则所需的权限取决于user值，如第25.6节，“存储对象访问控制”所述。If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as discussed in Section 25.7, “Stored Program Binary Logging”.如果启用了二进制日志记录，则CREATE TRIGGER可能需要SUPER权限，如第25.7节，“存储程序二进制日志记录”所述。

The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time, as described later in this section.DEFINER子句确定在触发器激活时检查访问权限时要使用的安全上下文，如本节后面所述。

trigger_time is the trigger action time. trigger_time是触发动作时间。It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.它可以是BEFORE或AFTER，指示触发器在要修改的每一行之前或之后激活。

Basic column value checks occur prior to trigger activation, so you cannot use BEFORE triggers to convert values inappropriate for the column type to valid values.基本列值检查在触发器激活之前进行，因此不能使用BEFORE触发器将不适合列类型的值转换为有效值。

trigger_event indicates the kind of operation that activates the trigger. trigger_event表示激活触发器的操作类型。These trigger_event values are permitted:允许使用以下trigger_event值：

The trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. trigger_event并不表示激活触发器的SQL语句的文本类型，而是表示一种表操作类型。For example, an INSERT trigger activates not only for INSERT statements but also LOAD DATA statements because both statements insert rows into a table.例如，INSERT触发器不仅为INSERT语句激活，而且还为LOAD DATA语句激活，因为这两个语句都将行插入表中。

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.一个可能令人困惑的例子是NSERT INTO ... ON DUPLICATE KEY UPDATE ...语法：BEFORE INSERT触发器针对每一行激活，然后是AFTER INSERT触发器或BEFORE UPDATE和AFTER UPDATE触发器，具体取决于该行是否有重复键。

Note注意

Cascaded foreign key actions do not activate triggers.级联外键操作不会激活触发器。

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. 例如，一个表可以有两个BEFORE UPDATE触发器。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 trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. 要影响触发器顺序，请指定一个trigger_order子句，该子句指示follower或prefore以及具有相同触发器事件和操作时间的现有触发器的名称。With FOLLOWS, the new trigger activates after the existing trigger. 使用FOLLOWS时，新触发器在现有触发器之后激活。With PRECEDES, the new trigger activates before the existing trigger.使用PRECEDES，新触发器在现有触发器之前激活。

trigger_body is the statement to execute when the trigger activates. trigger_body是触发器激活时要执行的语句。To execute multiple statements, use the BEGIN ... END compound statement construct. 要执行多个语句，请使用BEGIN ... END复合语句构造。This also enables you to use the same statements that are permitted within stored routines. 这还允许您使用存储例程中允许的相同语句。See Section 13.6.1, “BEGIN ... END Compound Statement”. 请参阅第13.6.1节，“BEGIN ... END复合语句”。Some statements are not permitted in triggers; see Section 25.8, “Restrictions on Stored Programs”.触发器中不允许使用某些语句；请参阅第25.8节，“存储程序的限制”。

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. 在触发器主体中，可以使用别名OLD和NEW来引用从表（与触发器关联的表）中的列。OLD.col_name refers to a column of an existing row before it is updated or deleted. 在更新或删除现有行之前，引用该行的列。NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.要插入的新行或更新后的现有行的列。

Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns. 触发器不能使用NEW.col_name或使用OLD.col_name引用生成的列。For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.有关生成列的信息，请参阅第13.1.20.8节，“CREATE TABLE和生成的列”。

MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.MySQL存储在创建触发器时生效的系统变量sql_mode设置，并始终在该设置生效的情况下执行触发器主体，而不管触发器开始执行时的当前服务器sql模式如何。

The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. DEFINER子句指定在触发激活时检查访问权限时要使用的MySQL帐户。If the DEFINER clause is present, the user value should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). 如果存在DEFINER子句，则user值应为指定为'user_name'@'host_name'的MySQL帐户、CURRENT_USER或CURRENT_user()。The permitted user values depend on the privileges you hold, as discussed in Section 25.6, “Stored Object Access Control”. 允许的user值取决于您拥有的权限，如第25.6节，“存储对象访问控制”所述。Also see that section for additional information about trigger security.有关触发器安全性的更多信息，请参阅该部分。

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE TRIGGER statement. 如果省略DEFINER子句，则默认的DEFINER是执行CREATE TRIGGER语句的用户。This is the same as specifying DEFINER = CURRENT_USER explicitly.这与显式指定DEFINER=CURRENT_USER相同。

MySQL takes the DEFINER user into account when checking trigger privileges as follows:MySQL在检查触发器权限时会考虑DEFINER用户，如下所示：

Within a trigger body, the CURRENT_USER function returns the account used to check privileges at trigger activation time. 在触发器主体内，CURRENT_USER函数返回用于在触发器激活时检查权限的帐户。This is the DEFINER user, not the user whose actions caused the trigger to be activated. 这是DEFINER用户，而不是其操作导致触发器被激活的用户。For information about user auditing within triggers, see Section 6.2.22, “SQL-Based Account Activity Auditing”.有关触发器内用户审核的信息，请参阅第6.2.22节，“基于SQL的帐户活动审核”。

If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in LOCK TABLES and Triggers.如果使用LOCK TABLES锁定具有触发器的表，则触发器中使用的表也会被锁定，如锁定表和触发器中所述。

For additional discussion of trigger use, see Section 25.3.1, “Trigger Syntax and Examples”.有关触发器使用的更多讨论，请参阅第25.3.1节，“触发器语法和示例”。