Triggers and Events: Performance Implications in Production

1. Why triggers look convenient at first glance

Triggers promise an appealing property: a rule is defined once at the database and then guaranteed to fire on every change, regardless of which application code, script, or direct SQL access triggered it. That looks especially attractive when several applications or scripts access the same database and a central, unavoidable point for logging or consistency checking is needed.

Precisely for that reason, teams under time pressure like to reach for a trigger to quickly retrofit missing logging or an extra validation, without having to touch every calling site in application code individually. That convenience has a price, though, one that only becomes visible under production load.

2. Hidden costs: every trigger invocation runs synchronously in the same transaction context

A trigger is not an asynchronous background job, it runs synchronously inside the same transaction as the statement that fired it. Only once the trigger has fully finished does the original INSERT, UPDATE, or DELETE count as complete, and every statement inside the trigger counts toward the transaction's total runtime, including every read and write against other tables.

For a multi-row DML statement, the trigger additionally runs once per affected row, not once per statement. A bulk insert of ten thousand rows with a trigger issuing one extra query per row against another table therefore results in ten thousand additional queries, invisible anywhere in the original application code.

-- Trigger that runs once per affected row on every INSERT
DELIMITER $$
CREATE TRIGGER trg_stock_after_insert
AFTER INSERT ON cataloginventory_stock_item
FOR EACH ROW
BEGIN
    INSERT INTO stock_change_log (product_id, qty, changed_at)
    VALUES (NEW.product_id, NEW.qty, NOW());
END$$
DELIMITER ;

-- A bulk import of 10,000 rows generates 10,000 extra inserts,
-- completely invisible in the calling application code.

3. A concrete example: a trigger on a frequently updated table with cascading effects

A trigger on the stock table that automatically updates a notification table or an aggregated statistics table on every quantity change looks unremarkable in a development environment with a handful of test rows. During a production stock reconciliation that updates thousands of products in a short window, for instance after a delivery or an external ERP sync, that same trigger can turn into the dominant cost factor of the entire operation.

It becomes particularly critical when the trigger itself fires writes against tables that carry their own triggers or foreign key constraints. Such cascading effects are hard to keep track of in practice, because a single UPDATE statement can end up triggering an entire chain of side effects across multiple tables, with none of that visible in the original SQL statement.

4. Locking implications: extended lock times and deadlock risk

Because trigger logic runs inside the same transaction, every extra query inside the trigger extends the time locks are held on the affected rows and indexes. On heavily used tables like cart or stock, that raises the likelihood of concurrent transactions having to wait on each other or running into a lock timeout.

If the trigger also touches tables locked in reverse order by other concurrently running transactions, the risk of classic deadlocks rises further. Since the lock order imposed by the trigger logic is often not obvious from the calling code, such deadlocks are particularly tedious to diagnose in practice.

5. Debugging difficulties: implicit logic with no easy tracing

MySQL offers no built-in step-by-step debugger for triggers, and errors inside a trigger frequently surface as a generic SQL error on the original INSERT or UPDATE, with no obvious hint that the actual cause lies in a trigger on a completely different table. Developers who only know the application code initially just see a failed, seemingly simple statement.

This implicit logic is particularly treacherous because it evades classic testability: application-level unit tests frequently do not simulate a real database with active triggers, so a side effect that only arises through a trigger stays invisible in the test suite and only surfaces in production or manual staging testing.

6. Events: the MySQL Event Scheduler as a database-internal cron replacement

The Event Scheduler allows recurring jobs to be defined directly in the database via CREATE EVENT ... ON SCHEDULE EVERY ..., without setting up an external cron job. It requires the globally enabled event_scheduler variable, without which defined events are stored but never actually run.

The advantage lies in the tight coupling to the data itself, for instance for regular cleanup work directly in the database. The disadvantage is noticeably weaker observability and alerting tooling than application job queues offer: error handling, retry logic, and monitoring largely have to be rebuilt by hand inside the event definition, while established job frameworks already provide that out of the box.

-- Daily event to clean up old log entries
CREATE EVENT IF NOT EXISTS ev_cleanup_stock_change_log
ON SCHEDULE EVERY 1 DAY STARTS '2026-08-09 03:00:00'
DO
    DELETE FROM stock_change_log
    WHERE changed_at < NOW() - INTERVAL 90 DAY;

-- The event scheduler must be enabled globally
SHOW VARIABLES LIKE 'event_scheduler';

7. When triggers make sense: audit logging and integrity that cannot be bypassed

Triggers justify themselves mainly where a rule truly must not be bypassed, even when someone writes directly to the database through an admin tool or a script. Audit logging that, for compliance reasons, must record every change to certain tables without exception is a classic example where a trigger offers the only guarantee that no change goes unnoticed.

Maintaining denormalized aggregate columns, deliberately kept redundant for performance reasons, can also justify a trigger when it must be absolutely guaranteed that they never drift out of sync, regardless of the write path used.

8. When application logic is the better choice

Business logic that changes frequently and needs to be tested, versioned, and reviewed by developers belongs in the application layer, not in a trigger. Application logic can be covered by unit tests, tracked cleanly in logs and observability tools, and managed in the application code's usual version control, all of which are noticeably harder to achieve for triggers.

If the logic additionally needs to reach external systems, such as updating a search index or sending a notification, that never belongs in a trigger in the first place, since database transactions are not designed for such external side effects, and a failure in the external call can, in the worst case, block the entire transaction.

9. Magento context: caution with triggers on frequently updated tables

In Magento shops, tables like catalog_product_entity, cataloginventory_stock_item, or sales_order and their related child tables are especially sensitive to custom triggers, because indexers, bulk imports, and batch processing regularly change very many rows there in a short time. An extra trigger on one of these tables multiplies its overhead exactly by the row count of such mass operations.

Before running a larger data import or an extensive reindex, it is worth deliberately checking whether legacy or third-party triggers exist on the affected tables, for instance via information_schema.triggers, to avoid surprises during the runtime of such batch operations. Observers or plugins within Magento are almost always the better, more testable, and more observable alternative to a database trigger for new requirements.

-- Check existing triggers on sensitive tables before a mass import
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
WHERE event_object_schema = DATABASE()
  AND event_object_table IN (
      'catalog_product_entity', 'cataloginventory_stock_item', 'sales_order'
  );

10. Summary

11. FAQ: Triggers and Events in MySQL: The Essentials at a Glance