Every database trigger is associated with a specific DML
DELETE) on a specific table—the trigger code
will execute whenever that DML operation occurs on that table.
Furthermore, all MySQL 5.0 triggers are of the
ROW type, which means that the trigger code will execute
once for each row affected by the DML operation. Given that a single
DML operation might potentially affect thousands of rows, should we be
concerned that our triggers might have a negative effect on DML
For all of the reasons outlined previously, triggers can significantly increase the amount of time taken to execute DML operations and can have a detrimental effect on overall application performance if trigger overhead is not carefully managed.
The overhead of a trigger itself is significant, though not unmanageable. For instance, consider the trigger shown in Example 22-21; this trivial trigger serves no purpose, but it allows us to measure the overhead of a trigger that does virtually nothing.
CREATE TRIGGER sales_bi_trg BEFORE INSERT ON sales FOR EACH ROW SET @x=NEW.sale_value;
When we implemented this trivial trigger, the time taken to insert 100,000 sales rows increased from 8.84 seconds to 12.9 seconds—an increase of about 45%. So even the simplest of triggers adds a significant—though bearable—overhead.
But what about a complex trigger? In Chapter 11, we created a set of triggers to maintain a sales ...