MySQL 5.1

Looking even further ahead, we expect continued improvements in MySQL 5.1, with the possible addition of another major feature that so far hasn’t been implemented yet. This is the implementation of triggers. Triggers are almost certain to make an appearance in MySQL 5.1.

We’ll give you a conceptual overview of triggers in the following section, and discuss what form we think they’re likely to take when they’re actually implemented. While it’s true that no one can tell for sure what may happen in the future, we do know that MySQL AB say they intend to implement these according to recognized standards, so what we will do is to show you what ANSI SQL says that triggers should be like, and make some observations on how they’re implemented in other database management systems.

Triggers

A trigger is a form of stored procedure that executes automatically when a specified event takes place—that is, when an SQL statement modifies data. Triggers are bound to specific types of statements ( UPDATE , INSERT , or DELETE ) acting on specific tables in much the same way that event handlers or event listeners are bound to particular events occurring on particular interface elements in GUI programming. For instance, a trigger might be associated with an INSERT statement acting on the products table which we’ve been using in our examples, or with an UPDATE statement that affects only the price column of that table.

In addition, a trigger is declared in such a way that it acts either before or after the statement to which it’s bound takes effect. If the trigger acts before the data-modification statement does, then it cannot “see” any changes caused by that statement; it only “knows” that the statement will affect a given table in a certain manner but not the specifics of that action. A trigger that acts after the triggering statement does see any changes caused by the statement and can act upon them.

Some databases (including both SQL Server and Oracle) also support triggers that act in place of the triggering statement using INSTEAD OF rather than BEFORE or AFTER for this purpose. At this time, MySQL is not expected to support this nonstandard extension. However, as it certainly supports other non-ANSI additions to SQL, it may be that demand for this feature will be sufficient to prompt its addition in a future release. (As always, don’t count on this happening, but don’t rule it out, either; and do watch for further developments in this area.)

It’s also important to understand that triggers operate at the statement level by default. In other words, a trigger normally executes once when a statement of the proper type acts on the proper table, even if that statement affects 3,000 rows. However, the standard does allow for row-level triggers as well. If MySQL supports this in its trigger implementation, then it will be possible for a trigger to execute as many times as there are rows affected by the data-modification statement.