Since triggers are still in the early stages of development for MySQL, users may find the number of limitations restrictive, warns Scott Noyes. Noyes, a senior Web applications developer for Bookit.com, goes on to compare trigger functionality in Oracle, MySQL and Microsoft SQL Server.
Discover what can trigger mutating table errors MySQL and the finer points between statement and row triggers.
What are the differences between statement triggers and row triggers?
Scott Noyes: Statement triggers "fire" once for each statement no matter how many rows that statement affects. Row triggers act for each row affected. A statement like "UPDATE myTable SET myField = 1" would fire a statement trigger only once. That same statement would cause a row trigger to fire as many times as there are rows in the table. MySQL currently only supports row triggers.
What are mutating table errors?
Noyes: Mutating table errors occur when a trigger attempts to access a "mutating" table, like the table from which the trigger has been called. In MySQL, triggers are not initiated by cascading foreign keys, and they cannot modify the table from which they are called, so neither of those issues can cause a mutating table error. However, foreign keys appear to operate before 'AFTER' triggers, so it is possible to create a trigger that will cause a statement to fail.
Which supports the most triggers, Microsoft SQL Server, Oracle or MySQL? How should that affect my decision in regards to choosing a database?
Noyes: Triggers are fairly new to MySQL and are, therefore, still in an early stage of development. There are a number of limitations you might find too restrictive if you rely heavily on triggers for your application. All three databases support row triggers before and after insert, update and delete statements. Oracle and Microsoft SQL Server also offer statement triggers.
MySQL allows, at most, one trigger per table per event time and type -- a table can only have one BEFORE INSERT trigger, for example. Both Orable and SQL Server permit multiple triggers of each type on a table. The order in which they execute may not be defined.
MySQL requires each of the six possible triggers to be defined separately. If you want to perform the exact same action on INSERT or on DELETE, you must define two separate triggers. Oracle and SQL Server allow trigger definitions to include multiple event types, such as BEFORE INSERT, DELETE
MySQL does not permit dynamic SQL in a trigger, or in a stored procedure called by a trigger. There is no such restriction in Oracle.
Both MySQL and Oracle attempt to prevent mutating table errors where, for example, a trigger attempts to modify the table which called it. MySQL and Oracle try to stop this by preventing such triggers from being created. It is still possible to create such errors, using cascading foreign keys or by other means. SQL Server allows for recursive triggers, and so must be coded carefully to prevent endless recursion.
Do you have questions about MySQL? Email Scott Noyes about your database issues.
