Questions

Questions and Answers

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

See 章 18. トリガ.

26.5.2: Is there a discussion forum for MySQL Triggers?

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

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

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

26.5.4: Are there any default triggers?

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?

In MySQL 5.1, triggers can be created using the CREATE TRIGGER statement, and dropped using DROP TRIGGER. See 項18.1. 「CREATE TRIGGER 構文」, and 項18.2. 「DROP TRIGGER 構文」, for more about these statements.

Information about triggers can be obtained by querying the INFORMATION_SCHEMA.TRIGGERS table. See 項21.16. 「INFORMATION_SCHEMA TRIGGERS テーブル」.

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

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 項21.16. 「INFORMATION_SCHEMA TRIGGERS テーブル」.

You can also use the SHOW TRIGGERS statement, which is specific to MySQL. See 項12.5.4.29. 「SHOW TRIGGERS 構文」.

26.5.7: Where are triggers stored?

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?

Yes. A table on a remote server could be updated using the FEDERATED storage engine. (See 項13.9. 「FEDERATED ストレージエンジン」).