< Day Day Up > 

Triggers introduce basic server-side event handling and control to your MySQL database by intercepting database activity before it can affect underlying information. Data-modifying events (INSERT, UPDATE, or DELETE) can activate a trigger, which is then processed either before or after the data-modifying event.

For example, you might want to perform a server-side calculation on information before it's written into the database. This scenario is discussed momentarily.

Current Trigger Limitations

At the time of this writing, there are a number of restrictions on what your triggers may accomplish. They include the following:

  • No transactional support Your triggers cannot take advantage of InnoDB's transactional capabilities with the START TRANSACTION, COMMIT, or ROLLBACK statements. This can significantly impact your databases' integrity.

  • No table names You cannot explicitly name any tables within a trigger. This means, for example, that you cannot copy information from one table to another or even examine the contents of another table. Actions such as information or event logging aren't possible.

  • No stored procedure invocations You might be thinking that you can avoid the preceding limitations by invoking a stored procedure. Unfortunately, this isn't possible: You cannot use the CALL statement within your triggers.

Despite these limitations (many of which are addressed in upcoming versions), triggers can still add performance value to your application. The following section lists some situations in which you should consider them versus situations in which they are not warranted.

When to Use Triggers

From a performance perspective, the primary reasons to use triggers given today's restrictions are similar to those of stored procedures: to remove processing from clients and centralize it on the database server.

For example, suppose that you're developing an application that records purchases at High-Hat's new airport souvenir stands and then calculates the sales tax due among several government entities:

 CREATE TABLE purchases (     purchase_id INTEGER AUTO_INCREMENT PRIMARY KEY,     iata_code CHAR(3),     amount DECIMAL(5,2),     federal_tax DECIMAL(5,2),     province_tax DECIMAL(5,2),     municipal_tax DECIMAL(5,2),     vat DECIMAL(5,2) ); 

You could encode this at the client, but this adds complexity and processing time, and might also introduce developer-caused inconsistencies if these calculations are written in two or more applications. Instead, you elect to use a trigger:

 DELIMITER !! CREATE TRIGGER t_insert BEFORE INSERT ON purchases FOR EACH ROW BEGIN     CASE NEW.iata_code ... ...         WHEN 'JFK' THEN SET NEW.municipal_tax = NEW.amount * .00235;         WHEN 'LHR' THEN SET NEW.municipal_tax = NEW.amount * .00350;         WHEN 'NYO' THEN SET NEW.vat = NEW.amount * .15; ... ...     END CASE; END!! DELIMITER ; 

You can now be assured that regardless of its source, any data that is inserted into the purchases table will have the proper taxes set. Of course, you are now responsible for keeping the trigger up to date.

When Not to Use Triggers

Whenever any new technology arrives, it's tempting to apply it in as many situations as possible. This is especially true with triggers. Even though they are currently quite constrained in terms of functionality, they can still streamline many types of operations and calculations.

However, triggers do add server-side overhead. The exact overhead amount is very dependent on the number and complexity of your triggers. For example, in the previous purchases instance, we added a trigger with eight options in the CASE statement. Next, we loaded 50,000 rows of random information via a series of INSERT statements. Overall, loading performance degraded by approximately 10%. For a large data-load operation, this might be significant. However, for an interactive transactional application, it's unlikely that the users would notice this overhead.

In general, don't let triggers serve as your last line of defense for data validation. The previous example shows a trigger setting a tax based on information provided at runtime. This is very different than a data integrity check, which might be faced with missing information. Given their limited capabilities, your ability to handle error conditions is quite constrained. Stored procedures are a much better choice for these kinds of activities.

     < Day Day Up > 

    MySQL Database Design and Tuning
    MySQL Database Design and Tuning
    ISBN: 0672327651
    EAN: 2147483647
    Year: 2005
    Pages: 131

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: