when will Innodb support activation of triggers on cascade foreign key? [closed]

Question

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

Currently, cascaded foreign key actions do not activate triggers.

Any Idea when will this be implemented.

Is there a work around for this?

Answer 1

Not to start a flamewar, but this does work in PostgreSQL for years. When using innoDB, it should be relative simple to move to PostgreSQL.

Answer 2

IMHO I do not think this feature will be implemented in the foreseeable future. Why?

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Always keep in mind that a Trigger can require major overhead. In fact, According to this book

MySQL Stored Procedure Programming, page 256 under the heading "Trigger Overhead" says the following:

It is important to remember that, by necessity, triggers add overhead to the DML statement to which they apply. the actual amount of overhead will depend upon the nature of the trigger, but --- as all MySQL triggers execute FOR EACH ROW --- the overhead can rapidly accumulate for statements that process large numbers of rows. You should therefore avoid placing any expensive SQL statements or procedural code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:

The lesson here is this: since the trigger code will execute once for every row affected by a DML statement, the trigger can easily become the most significant factor in DML performance. Code inside the trigger body needs to be as lightweight as possible and -- in particular -- any SQL statements in the trigger should be supported by indexes whenever possible.

I also explained other nasty aspects of Triggers in an earlier post.

In light of these things, until Triggers can be redesigned to handle Transaction Isolation, I do not see Triggers being tied into CASCADE operations anytime soon.