Questions

Questions and Answers

22.5.1: Can a trigger call a stored procedure?

Yes.

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

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.

22.5.3: Is it 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 Section 13.7, “The FEDERATED Storage Engine”).

22.5.4: How are triggers managed in MySQL?

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

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

22.5.5: Where are triggers stored?

Triggers for a table are currently stored in .TRG files, with one such file one per table.

22.5.6: 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.

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

See Section 18.3, “Using Triggers”.

22.5.8: Is there a discussion forum for MySQL Triggers?

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

22.5.9: How are actions carried out through triggers on a master replicated to a slave?

First, the triggers that exist on a master must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication. For example, consider a table EMP that has an AFTER insert trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave server as well. The replication flow would be:

For more information, see Section 16.4.1.25, “Replication and Triggers”.

22.5.10: 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 Section 19.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

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

22.5.11: Do triggers work with replication?

Triggers and replication in MySQL 5.0 work in the same way as in most other database systems: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.

For more information, see Section 16.4.1.25, “Replication and Triggers”.

22.5.12: Can triggers access tables?

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Before MySQL 5.0.10, a trigger cannot modify other tables.)

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

Yes. For example, a trigger could invoke the sys_exec() UDF available at MySQL Forge here: http://forge.mysql.com/projects/project.php?id=211