触发器

Questions

Questions and Answers

26.5.1:
Where can I find the documentation for MySQL 5.0 triggers?

详情请看 Chapter 18, Triggers.

See Chapter 18, Triggers.

26.5.2:
Is there a discussion forum for MySQL Triggers?

是的.详情请看 http://forums.mysql.com/list.php?99.

Yes. It is available at
http://forums.mysql.com/list.php?99.

26.5.3:
Does MySQL 5.0 have statement-level or
row-level triggers?

在MySQL 5.0中,所有的触发器都是 FOR EACH ROW 的 - 意思是,触发器可作用于每次插入, 更新, 删除的姬路.MySQL 5.0触发器还不支持使用 FOR EACH STATEMENT 语句.

In MySQL 5.0, all triggers are FOR
EACH ROW — that is, the trigger is activated
for each row that is inserted, updated, or deleted. MySQL
5.0 does not support triggers using
FOR EACH STATEMENT.

26.5.4:
Are there any default triggers?

显然没有.MySQL的 TIMESTAMP 字段则有特定的动作,定义为 AUTO_INCREMENT 的字段也是如此.

Not explicitly. MySQL does have specific special behavior
for some TIMESTAMP columns, as well as
for columns which are defined using
AUTO_INCREMENT.

26.5.5:
How are triggers managed in MySQL?

MySQL 5.0中,触发器用 CREATE TRIGGER 语句来创建,用 DROP TRIGGER 语句来删除.详情请看 Section 18.1, “CREATE TRIGGER Syntax”, 和 Section 18.2, “DROP TRIGGER Syntax”.

查询 INFORMATION_SCHEMA.TRIGGERS 表就可以取得触发器的信息,详情请看 Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

In MySQL 5.0, triggers can be created using the
CREATE TRIGGER statement, and dropped
using DROP TRIGGER. See
Section 18.1, “CREATE TRIGGER Syntax”, and
Section 18.2, “DROP TRIGGER Syntax”, for more about
these statements.

Information about triggers can be obtained by querying the
INFORMATION_SCHEMA.TRIGGERS table. See
Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

26.5.6:
Is there a way to view all triggers in a given database?

是的.在INFORMATION_SCHEMA.TRIGGERS 中使用以下语句就能查看 dbname 下所有的存储过程了.

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
    FROM INFORMATION_SCHEMA.TRIGGERS 
    WHERE TRIGGER_SCHEMA='dbname';

详情请看 Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

同样,也可以在MySQL中执行 SHOW TRIGGERS 语句,详情请看 Section 13.5.4.24, “SHOW TRIGGERS Syntax”.

Yes. You can obtain a listing of all triggers defined on
database dbname using a query on the
INFORMATION_SCHEMA.TRIGGERS table such as the one shown
here:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
    FROM INFORMATION_SCHEMA.TRIGGERS 
    WHERE TRIGGER_SCHEMA='dbname';

For more information about this table, see
Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

You can also use the SHOW TRIGGERS
statement, which is specific to MySQL. See
Section 13.5.4.24, “SHOW TRIGGERS Syntax”.

26.5.7:
Where are triggers stored?

触发器当前存储为 .TRG 文件,每个数据表一个文件.换句话说,每个 .TRG 文件属于某个表.

将来,我们打算改变这种做法,把触发器也保存在 .FRM 文件中,这个文件也定义了数据表结构.我们也打算把触发器作为数据库级的对象 - 而不是现在的数据表级,使之和SQL标准兼容.

Triggers are currently stored in .TRG
files, with one such file one per table. In other words, a
trigger belongs to a table.

In the future, we plan to change this so that trigger
information will be included in the
.FRM file that defines the structure of
the table. We also plan to make triggers database-level
objects — rather than table-level objects as they are
now — to bring them into compliance with the SQL
standard.

26.5.8:
Can a trigger call a stored procedure?

是的.

Yes.

26.5.9:
Can triggers access tables?

触发器可以访问它自己的数据表中新的和旧的数据.通过存储过程,或者多表更新/删除语句,触发器同样可以影响到其他数据表.

A trigger can access both old and new data in its own table.
Through a stored procedure, or a multi-table update or
delete statement, a trigger can also affect other tables.

26.5.10:
Can triggers call an external application through a UDF?

现在还不行.

No, not at present.

26.5.11:
Is possible for a trigger to update tables on a remote
server?

是的.一个在远程服务器的数据表也可以被更新,如果它使用 FEDERATED 存储引擎的话.详情请看 Section 14.7, “The FEDERATED Storage Engine”.

Yes. A table on a remote server could be updated using the
FEDERATED storage engine. (See
Section 14.7, “The FEDERATED Storage Engine”).