No More Silent Foreign Key Cascades: MySQL 9.7 Lets Child Triggers Speak Up

MySQL 9.7 introduces a long-requested improvement: Child table triggers are executed during SQL-layer foreign key cascades. Historically, cascades executed inside InnoDB did not invoke child table triggers, which created gaps in auditing, derived data maintenance, and observability.

When a parent row change triggered cascading changes in child tables, those child table triggers were not executed. This behavior often surprised developers who relied on triggers for auditing, or enforcing business rules. This change significantly improves correctness and observability while maintaining backward compatibility through an explicit opt-in mechanism.

The Problem: Cascades Happened, But Triggers Stayed Silent

In MySQL, when a parent row is updated or deleted, foreign key constraints may cause cascading changes in child tables. For example, ON DELETE CASCADE removes dependent rows, and ON UPDATE CASCADE updates foreign key references automatically.

However, historically, these cascade-driven changes did not execute triggers on child tables. This behavior has been a long-standing limitation in MySQL, as documented in MySQL Bug #11472, where cascade operations do not invoke triggers on affected child tables. As a result, systems relying on triggers for auditing or business logic can silently miss critical changes introduced via foreign key cascades.

From a data perspective, the changes happened correctly, but from a trigger perspective, those changes were invisible.

This created several real-world issues:

• Audit triggers on child tables missed cascade-driven changes.
• Materialised or denormalised columns maintained by triggers became stale.
• Business rules encoded in triggers did not execute.
• Debugging and observability became difficult.

Developers often expected that if a row changed, triggers would fire, regardless of whether the change came directly from a SQL statement or from a foreign key cascade. This difference between expected and actual behavior sometimes led to unintended outcomes in production systems.

Example:

Consider a simple parent-child relationship with a trigger on the child table:

CREATE TABLE child (cid INT PRIMARY KEY, parent_id INT, 
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE)

CREATE TABLE audit_log (message VARCHAR(255))

INSERT INTO parent VALUES (1) INSERT INTO child VALUES (1,1)

CREATE TRIGGER child_delete_trigger AFTER DELETE ON child 
FOR EACH ROW INSERT INTO audit_log VALUES ('Child row deleted')

Behavior before MySQL 9.7:

When the parent row is deleted:

DELETE FROM parent WHERE id = 1

Before MySQL 9.7, the child row is deleted due to the cascade, but the trigger does not get executed. As a result, the audit table remains empty. This behavior is technically correct from a foreign key standpoint, but unexpected from a trigger standpoint.

Behavior in MySQL 9.7
MySQL 9.7 introduces the ability to execute triggers during SQL-layer foreign key cascades. You can enable it by setting the new MySQL server variable enable_cascade_triggers.

After enabling the feature:

SET enable_cascade_triggers = ON

Running the same delete statement:

DELETE FROM parent WHERE id = 1

Now produces following behavior:

• Child row is deleted
• Child trigger is executed
• Audit record is inserted

This makes cascade-driven changes visible to trigger logic, aligning MySQL behavior with user expectations.

The Solution: Make changes visible to trigger

Historically, foreign key cascades were handled inside the InnoDB storage engine. Because triggers are implemented at the SQL layer, engine-level cascades could not invoke triggers.

Starting with MySQL 9.6, MySQL introduced SQL-layer foreign key handling, controlled by server startup variable innodb_native_foreign_keys:

When SQL-layer foreign keys are enabled: innodb_native_foreign_keys = OFF(Default)

• Cascades are executed in the server layer
• Trigger dispatch becomes possible
• Recursion and cascade chains can be tracked
• Safety limits can be enforced

This architectural change made trigger execution during cascades technically feasible.

Which Child Table Triggers Fire During Cascades

When cascade trigger execution is enabled, MySQL executes both BEFORE and AFTER triggers on child tables. The behavior depends on the cascade type:

• ON DELETE CASCADE → DELETE triggers are executed
• ON UPDATE CASCADE → UPDATE triggers are executed
• ON DELETE SET NULL → UPDATE triggers are executed
• ON UPDATE SET NULL → UPDATE triggers are executed

This ensures that any trigger logic relying on row changes is executed consistently.

Trigger Execution Order

Trigger execution order is important, particularly when both parent and child tables have triggers. When a parent row deletion causes a cascade, MySQL executes triggers in a predictable sequence.

Execution proceeds as follows:

1. DELETE FROM parent WHERE id = 1
2. Call BEFORE DELETE trigger of parent table
3. Cascade to child table
4. Call BEFORE DELETE trigger of child table
5. Delete child row
6. Call AFTER DELETE trigger of child table
7. Delete parent row
8. Call AFTER DELETE trigger of parent table

This deterministic ordering ensures that triggers behave consistently and predictably. In case of multi level cascades, parent → child → grandchild, when a parent row is deleted, cascades propagate through the chain following depth-first execution.

Transaction Behavior

Cascade-trigger execution is part of the same SQL statement and transaction. If any trigger fails during cascade execution, the entire statement is rolled back.

For example, if a child trigger raises an error:

• Parent deletion is aborted
• Child changes are rolled back
• Transaction remains consistent

This preserves ACID guarantees and ensures data integrity.

Safety Controls and Recursion Limits

To prevent recursive cascades and unintended side effects, if a trigger defined on a child table attempts to modify the parent table, or otherwise re-enters the same cascade path, the server detects the condition and reports an error.

Foreign key cascades combined with triggers can create complex dependency chains. For example, a trigger may modify another table, which in turn triggers additional cascades. To keep cascading operations predictable and manageable, MySQL enforces a limit on how many tables can participate in cascade chains during a single statement execution. If the total number of tables involved across all cascading paths exceeds the maximum allowed limit (default value is 30), the server stops further processing and reports an error.

This safeguard helps prevent overly complex cascade chains, which could otherwise lead to difficult-to-debug behavior, excessive resource usage. If cascade depth exceeds this limit, MySQL aborts the statement and rolls back the transaction. 

Safe Adoption using server opt-in variable

Some applications may depend on triggers not firing during cascades. Enabling cascade trigger execution may change application behavior, especially for audit logging or business rule enforcement.

For this reason, the feature is opt-in. It is recommended to:

• Enable per session first
• Test thoroughly
• Gradually roll out

To preserve backward compatibility, cascade trigger execution is disabled by default. Users must explicitly enable it using the system variable, enable_cascade_triggers.

Configuration Variables

The feature is controlled using two variables:

• enable_cascade_triggers
• innodb_native_foreign_keys

The variable enable_cascade_triggers is dynamic and can be configured per session, allowing gradual rollout and testing.

Child Table Trigger Execution Behavior:

innodb_native_foreign_keys	enable_cascade_triggers	Child table trigger execution
OFF(Default)	OFF(Default)	No
OFF	ON	Yes
ON	OFF	No
ON	ON	No

When Should You Enable Cascade Triggers?

Enable cascade triggers when:

• Auditing is implemented using triggers
• Derived tables depend on triggers
• Business logic relies on child table triggers
• Observability of cascade changes is required

Avoid enabling when:

• Very large cascade operations occur frequently
• Triggers contain heavy logic
• Multi-level cascades are common

Summary

Foreign keys should not create hidden changes, and neither should triggers remain silent during those changes. This issue tracked in MySQL Bug #11472 for nearly 20 years has now finally been addressed in MySQL 9.7 by enabling trigger execution during SQL-layer foreign key cascades.

This improvement enhances:

• Observability
• Audit-ability
• Data consistency
• Predictability

By keeping the feature opt-in and providing safety limits, MySQL balances correctness with backward compatibility. For applications relying on triggers and foreign keys together, this enhancement represents a meaningful step forward in MySQL’s relational integrity model.