Questions
23.5.1: Can a trigger call a stored procedure?
23.5.2: Does MySQL 5.5 have statement-level or row-level triggers?
23.5.3: How are actions carried out through triggers on a master replicated to a slave?
23.5.4: Do triggers work with replication?
23.5.5: Is it possible for a trigger to update tables on a remote server?
23.5.6: How are triggers managed in MySQL?
23.5.7: Where are triggers stored?
23.5.8: Are there any default triggers?
23.5.9: Where can I find the documentation for MySQL 5.5 triggers?
23.5.10: Is there a discussion forum for MySQL Triggers?
23.5.11: Is there a way to view all triggers in a given database?
23.5.12: Can triggers access tables?
23.5.13: Can triggers call an external application through a UDF?
Questions and Answers
23.5.1: Can a trigger call a stored procedure?
Yes.
23.5.2: Does MySQL 5.5 have statement-level or row-level triggers?
In MySQL 5.5, all triggers are FOR EACH
ROW
— that is, the trigger is activated for each
row that is inserted, updated, or deleted. MySQL
5.5 does not support triggers using FOR
EACH STATEMENT
.
23.5.3: How are actions carried out through triggers on a master replicated to a slave?
Again, this depends on whether you are using statement-based or row-based replication.
Statement-based replication.
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:
Row-based replication. When you use row-based replication, the changes caused by executing the trigger on the master are applied on the slave. However, the triggers themselves are not actually executed on the slave under row-based replication. This is because, if both the master and the slave applied the changes from the master and — in addition — the trigger causing these changes were applied on the slave, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.
In most cases, the outcome is the same for both row-based and statement-based replication. However, if you use different triggers on the master and slave, you cannot use row-based replication. (This is because the row-based format replicates the changes made by triggers executing on the master to the slaves, rather than the statements that caused the triggers to execute, and the corresponding triggers on the slave are not executed.) Instead, any statements causing such triggers to be executed must be replicated using statement-based replication.
For more information, see Section 16.4.1.29, “Replication and Triggers”.
23.5.4: Do triggers work with replication?
Yes. However, the way in which they work depends whether you are using MySQL's “classic” statement-based replication available in all versions of MySQL, or the row-based replication format introduced in MySQL 5.1.
When using statement-based replication, triggers on the slave are executed by statements that are executed on the master (and replicated to the slave).
When using row-based replication, triggers are not executed on the slave due to statements that were run on the master and then replicated to the slave. Instead, when using row-based replication, the changes caused by executing the trigger on the master are applied on the slave.
For more information, see Section 16.4.1.29, “Replication and Triggers”.
23.5.5: 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.11, “The FEDERATED
Storage Engine”).
23.5.6: How are triggers managed in MySQL?
In MySQL 5.5, triggers can be created using the
CREATE TRIGGER
statement, and
dropped using DROP TRIGGER
. See
Section 12.1.15, “CREATE TRIGGER
Syntax”, and
Section 12.1.24, “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”.
23.5.7: Where are triggers stored?
Triggers for a table are currently stored in
.TRG
files, with one such file one per
table.
23.5.8: 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
.
23.5.9: Where can I find the documentation for MySQL 5.5 triggers?
See Section 18.3, “Using Triggers”.
23.5.10: Is there a discussion forum for MySQL Triggers?
Yes. It is available at http://forums.mysql.com/list.php?99.
23.5.11: 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.39, “SHOW TRIGGERS
Syntax”.
23.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.
23.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
User Comments
To work around not being able to invoke external applications directly from within triggers, the MySQL Message Queue API seeks to provide a reliable means for communicating with them.
For more information, see http://forge.mysql.com/wiki/ProjectPage_MySQL_Message_API
Add your own comment.