WL#17024: Activate triggers on referencing tables during foreign key CASCADE

Currently, MySQL does not support the execution of triggers on child
tables when rows are modified as a result of cascading UPDATE or
DELETE foreign key actions. Goal of this WL is to enable trigger(s)
execution on child tables during cascading  UPDATE or DELETE
operations initiated by Foreign key constraints.

With the implementation of Foreign Key Handling in SQL layer, 
which brought foreign enforcement and cascades to the server (SQL)
layer, firing triggers during such cascades is now technically feasible.
This WL should ensure that both BEFORE and AFTER triggers on child
tables are executed for rows modified through cascading action by SQL layer 
foreign key handling.

Note: Trigger execution for CASCADE actions in Storage Engine's
Foreign Key handling is out of this WL scope.

REFERENCES
==========
BUG#11472 - Triggers not executed following foreign key updates/deletes

Functional Requirements:
--------------------------
FR 1: Triggers must be fired for foreign key CASCADE action in SQL Layer
Foreign Key Handling.

  FR 1.1: For ON DELETE CASCADE action, during cascaded DELETE operation
  on child tables, BEFORE and AFTER triggers for DELETE action defined on
  child tables must be fired for each row.

  FR 1.2: For ON UPDATE CASCADE action, during cascaded UPDATE operation
  on child tables, BEFORE and AFTER triggers for UPDATE action defined on
  child tables must be fired for each row.

  FR 1.3: For ON DELETE SET NULL action, during cascaded UPDATE operation
  to SET NULL on child tables, BEFORE and AFTER triggers for UPDATE action
  defined on child tables must be fired for each row.

  FR 1.4: For ON UPDATE SET NULL action, during cascaded UPDATE operation
  to SET NULL on child tables, BEFORE and AFTER triggers for UPDATE action
  defined on child tables must be fired for each row.


FR 2: New system variable "enable_cascade_triggers" must be introduced
to enable or disable triggers execution for foreign key cascade actions.

Properties of variable:
  Name: enable_cascade_triggers
  Data Type: Boolean
  Default value: FALSE
  Scope: SESSION
  Mutable Type: Dynamic
  Command Line Option: Yes
  Persisted: Yes
  SET_VAR Hint:  Not Applicable
  BinLog: Yes

Note: This variable must be marked for deprecation and should be removed
in the future releases.

  FR 2.1: When innodb_native_foreign_keys = ON, enable_cascade_triggers
  variable must not have any effect; foreign keys and cascades remain
  handled inside InnoDB with no SQL-layer cascade-trigger firing.
  Note:A warning must be reported if enable_cascade_triggers is set
  to ON.

  FR 2.2: When innodb_native_foreign_keys = OFF and 
  enable_cascade_triggers = ON, triggers on rows affected by SQL-layer
  FK cascades must be fired.

  FR 2.3: When innodb_native_foreign_keys = OFF and
  enable_cascade_triggers = OFF, child table triggers must not fire
  during cascade changes.


FR 3: Parent table containing multiple foreign keys:
If parent table contains multiple foreign keys to different child tables,
corresponding triggers should be fired for all child tables if defined.


FR 4: Child table containing multiple triggers:
  
  FR 4.1: If child table contains multiple triggers, all triggers on
  child table must be fired.

  FR 4.2: Triggers must be executed in the defined execution order.


FR 5: Trigger execution order in case of multi level foreign keys:

  FR 5.1: In case of multi level foreign keys, BEFORE triggers must
  be executed in top-down cascade order: BEFORE trigger of parent table
  must be executed first, then BEFORE trigger of child must be executed.  

  FR 5.2: In case of multi level foreign keys, AFTER triggers must be
  executed in bottom-up cascade order: AFTER trigger of child table
  must be executed first, then AFTER trigger of parent must be executed.  


FR 6: NEW and OLD aliases.

  FR 6.1: In UPDATE trigger fired due to cascade actions, NEW and OLD
  aliases must refer to the post-cascade and pre-cascade values
  respectively. This applies to both SET NULL and UPDATE CASCADE actions.

  FR 6.2: In DELETE trigger fired due to cascade actions, OLD alias
  must refer the value being deleted.


FR 7: Error handling and atomicity

  FR 7.1: If any trigger execution (BEFORE/AFTER at any cascade level)
  fails or signals an error, the entire statement must fail and all
  cascaded changes must be rolled back.

  FR 7.2: For multi-level cascades, errors at any depth must roll back
  changes across all affected levels.

FR 8: Recursion and loop prevention

  FR 8.1: For self-referencing foreign keys, triggers must fire on rows
  in the same table affected by the cascade.

  FR 8.2: Circular foreign key must succeed and triggers should be fired
  only once for tables involved in circular foreign key.

  FR 8.3: Cascade-induced trigger execution must not allow infinite loops.
  If trigger definition on a child attempts to modify the parent table
  (or otherwise re-enters the same cascade path), the server must
  detect and report an error.

  FR 8.4: If cascades exceed the maximum depth (i.e. 15), then error
  ER_FK_DEPTH_EXCEEDED must be reported and the statements executed
  must be rolled back.

  FR 8.5: If number of tables that can participate collectively in all
  cascade chains exceeds max limit (i.e. 30) during a single statement
  execution, then an error must be reported.

  FR 8.6:  Multi-level, trigger-initiated cascades must function
  correctly.  When a child table trigger fires during a FK cascade,
  it may initiate further FK cascades to other related tables. It must
  ensure referential integrity and proper execution order for all
  triggered and cascading actions.

FR 9: Binary logging
Source must log all the changes performed inside cascade-induced triggers
of child table.

FR 10: Replication behaviour

  FR 10.1: In Row Based Replication mode, triggers must not be executed on
  the replica as a consequence of cascades applied by the applier thread.

  FR 10.2: In Statement Based Replication mode, the binlog applier thread
  in replica node must use the source setting of enable_cascade_triggers
  variable.

    FR 10.2.1:Source(OFF) -> Replica(OFF)
    If both the source and replica nodes uses enable_cascade_triggers=OFF,
    applier must not execute child table triggers during FK cascade.

    FR 10.2.2:Source(OFF) -> Replica(ON)
    If source node uses enable_cascade_triggers=OFF and replica uses
    enable_cascade_triggers=ON, applier must not execute child triggers
    during FK cascade.

    FR 10.2.3:Source(ON) -> Replica(OFF)
    If source node uses enable_cascade_triggers=ON and replica uses
    enable_cascade_triggers=OFF, applier must execute child triggers
    during FK cascade.

    FR 10.2.4:Source(ON) -> Replica(ON)
    If both the source and replica nodes uses enable_cascade_triggers=ON,
    applier must execute child table triggers during FK cascade.

FR 11: SQL Statement Interactions
  
  FR 11.1: INSERT...ON DUPLICATE KEY UPDATE on the parent that leads to
  UPDATE CASCADE must fire UPDATE triggers on affected child rows.

  FR 11.2: REPLACE on the parent that leads to a DELETE followed by INSERT
  statement must fire DELETE triggers on affected parent rows 
  and cascade DELETE triggers on children.

  Note: The subsequent parent INSERT does not re-insert child rows unless
  the DML explicitly does so (documented behaviour).

  FR 11.3: Foreign key checks on tables used by trigger bodies must follow
  standard foreign_key_checks semantics.

  FR 11.4: LOCK TABLE must work with foreign key cascade trigger firing.

  FR 11.5: Child table triggers calling FUNCTION referring to parent table
  must return error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG.


Non-functional requirements (NFRs):
-----------------------------------
NFR 1: No performance difference must be observed if triggers are not
defined on the child table or when enable_cascade_triggers=0 or when
using InnoDB native foreign key handling.

The primary goal of this WL is to execute triggers on child tables when
rows are modified as a result of cascading UPDATE and DELETE actions
initiated by foreign key constraint with SQL layer FK handling (i.e with
innodb_native_foreign_keys = OFF).

Executing trigger for FK handling in Storage Engines is out of this WL
scope.

1. Enabling/Disabling cascade-induced trigger execution:
--------------------------------------------------------
A new SESSION scope system variable, 'enable_cascade_triggers' is
introduced as part of this WL. Properties of this variable:

  Name: enable_cascade_triggers
  Data Type: Boolean
  Default value: FALSE
  Scope: SESSION
  Mutable Type: Dynamic
  Command Line Option: Yes
  Persisted: Yes
  SET_VAR Hint:  Not Applicable
  BinLog: Yes

With SQL FK handling enabled, cascade-induced child triggers are
not executed by default. Users can set enable_cascade_triggers to 
TRUE to ensure that triggers on child tables are executed during
foreign key cascades performed by the SQL engine.

Note: "enable_cascade_triggers" variable is marked for deprecation
and will be removed in the future releases.

Whenever 'enable_cascade_triggers' is set (either true or false), a
deprecation warning will be emitted, indicating that this variable will
be removed in a future release. If set to false (or 0), an additional
warning is reported to inform the user that triggers will not fire on
the child table during foreign key cascades.

2. Cascade-induced Trigger execution Semantics:
-----------------------------------------------
Cascade-induced triggers are executed as part of the same DML statement
resulting in cascade action. The trigger execution sequence on the child
table for various cascade actions is as follows:

ON DELETE CASCADE: 
  Fire BEFORE DELETE trigger on the child table.
  Delete the child row.
  Fire AFTER DELETE trigger on the child table.

ON UPDATE CASCADE | SET NULL and ON DELETE SET NULL:
  Fire BEFORE UPDATE trigger on the child table.
  Update the child row (to the new parent value or NULL).
  Fire AFTER UPDATE trigger on the child table.

When BEFORE triggers are fired during cascade, OLD values will be
available in the trigger for DELETE operation. For update, both OLD
and NEW values will be available.

  2.1 Multi Level Cascades and Trigger Time(BEFORE/AFTER):
  --------------------------------------------------------
  For cascades spanning multi-level, the triggers are executed in the
  following order:

   2.1.a) BEFORE trigger: Triggers with BEFORE time type are executed
   in the top-down order. BEFORE trigger defined on the parent table
   is executed first and then the BEFORE trigger from child table is
   executed.

   2.1.b) AFTER trigger: Triggers with AFTER time type execute in the
   bottom-up order. AFTER trigger defined on the child table is
   executed first and then the AFTER trigger from parent table is
   executed.

  2.2 Trigger execution order:
  ----------------------------
  If multiple triggers are defined on the child table, then while
  executing cascade-induced triggers, triggers are executed in the
  defined order.

3. FK_MAX_TABLES_IN_CASCADE_CHAINS:
-----------------------------------
Cascading operations across multiple levels are limited by
FK_MAX_CASCADE_DEPTH (default: 15). However, when triggers are executed
as a result of cascading actions, multiple cascade chains can be
created within a single statement. This can lead to increased memory
usage.

To mitigate excessive memory consumption, FK_MAX_TABLES_IN_CASCADE_CHAINS
is introduced (default: 30). This constant sets the maximum number of
tables that can participate collectively in all cascade chains during
a single statement.

If the total number of tables involved in cascading chains exceeds
this limit in a statement, an error will be reported and the operation
will be aborted.

4. Behaviour with foreign_key_checks variable:
----------------------------------------------
Cascades are performed only when FK checks are enforced for the
statement. If foreign_key_checks=0, neither enforcement nor cascades
occur, and no triggers on child table is fired.

Existing behaviour for skipping foreign key checks on binlog events
generated during cascade operation remains unchanged.

5. Import/Export:
-----------------
No Impact, as cascades are applicable only to UPDATE and DELETE
statements. Importing from logical dump containing mostly INSERT
operations, so it will not be affected.

6. In-place Upgrade:
---------------------
No Impact, as enable_cascade_triggers default value is FALSE.

7. Replication:
---------------
  7.1 Row based replication:
  -------------------------- 
  On replica nodes, worker threads explicitly set enable_cascade_triggers=0
  while applying row binlogs, which ensures triggers do not fire for
  cascades resulting from row based replication.

  7.2 Statement based replication: 
  -------------------------------
  The binlog applier thread in replica node uses the source setting of
  enable_cascade_triggers variable.
  With enable_cascade_triggers=ON/OFF, behavior will be as below

    Source(OFF) -> Replica(OFF): Child table triggers are not executed
    Source(OFF) -> Replica(ON) : Child table triggers are not executed.
    Source(ON)  -> Replica(OFF): Child table triggers are executed on both
                                 source and replica.
    Source(ON)  -> Replica(ON) : Child table triggers are executed on both
                                 source and replica.

  7.3  Replication Interoperability(SQL FK vs InnoDB FK):
  -------------------------------------------------------
  Child table triggers on FK cascade operations are executed only with SQL
  FK(i.e. innodb_native_foreign_keys=OFF).
  If source and replica use different FK handling modes, behavior depends
  on replication format.

    Row based replication: Replica applies changes using the same FK
    handling method as source. When using SQL FK with 
    enable_cascade_triggers, triggers are not executed on replica. 
    Events from the source are applied as is on replica. So row based
    replication works as expected.

    Statement based replication: With statement based replication, applier
    threads uses its local FK handling (i.e. innodb_native_foreign_keys)
    and source setting of enable_cascade_trigger variable. 

    With different FK handling between source and replica, 
    enable_cascade_trigger behavior for statement based replication is as below,
      1. Source (SQL FK, enable_cascade_trigger=OFF) -> Replica (InnoDB FK)
        Child table triggers are *not* executed on both Source and Replica.
      2. Source (InnoDB FK, enable_cascade_trigger = OFF) -> Replica (SQL FK)
        Child table triggers are *not* executed on both Source and Replica.
      3. Source (InnoDB FK, enable_cascade_trigger=ON) -> Replica (SQL FK)
        Child table triggers are *not* executed on both Source and Replica.
        Variable enable_cascade_trigger is replicated with value OFF in
        this case.
      4. Source (SQL FK, enable_cascade_trigger=ON) -> Replica (InnoDB FK)
        Child table triggers are executed on Source but *not* executed on
        the replica. This is not a recommended configuration.
            
      Case 4 is not a recommended configurations, but if deployment            
      ends up in this configuration then, following steps should be used
      to recover from it.
          1. Set source to read-only.
          2. Wait until all events are applied at the replica.
          3. Restart source and replica with a consistent FK handling mode:
            both SQL FK (innodb_native_foreign_keys=OFF), or
            both InnoDB FK (innodb_native_foreign_keys=ON).

  7.4 OLD→NEW Replication:
  ------------------------
  This replication setup is safe as enable_cascade_triggers=OFF on NEW,
  which ensures triggers do not fire for cascades resulting from
  replication. So there won't be any data inconsistency.

8. Performance impact:
------------------------
No performance difference is observed if triggers are not defined on the
child table or when enable_cascade_triggers=0 or when using InnoDB native
foreign key handling.

If triggers are definer, then enabling this feature introduces additional 
trigger dispatch overhead per cascaded row on affected child tables. The 
performance impact is proportional to the number of child rows affected by 
cascades and trigger complexity.