Trigger was not getting fired because record was getting deleted due to cascade delete operation. Currently, cascaded foreign key actions do not activate triggers in MySQL InnoDB . It is not supported by MySQL.
To work around for this, I have use Cascade trigger concept. (ie chain of triggers).
We have Parent table and child table with cascade delete implemented so that respective entry get deleted from the child.
as per this work around, We need to remove this cascade delete constraint and need to add trigger which do exactly same thing. We also need to write one more trigger on child table which will update the segment id of respective type driver table. This way, Whenever master record get deleted the child entry will be deleted because of the first trigger. This delete operation will activate trigger written on Child table and update the respective "type driver table" by resetting value to 0.
I have successfully implemented and tested this approach locally.
Trigger was not getting fired because record was getting deleted due to cascade delete operation. Currently, cascaded foreign key actions do not activate triggers in MySQL InnoDB . It is not supported by MySQL.
To work around for this, I have use Cascade trigger concept. (ie chain of triggers).
We have Parent table and child table with cascade delete implemented so that respective entry get deleted from the child.
as per this work around, We need to remove this cascade delete constraint and need to add trigger which do exactly same thing. We also need to write one more trigger on child table which will update the segment id of respective type driver table. This way, Whenever master record get deleted the child entry will be deleted because of the first trigger. This delete operation will activate trigger written on Child table and update the respective "type driver table" by resetting value to 0.
I have successfully implemented and tested this approach locally.
References:
Database : neutron_ml2 segments allocations, ml2_vxlan_ allocations, ml2_gre_allocations (depends on
network type).
Parent table : networks
Child table : ml2_network_
type driver tables : ml2_vlan_
Let me know your suggestion. If it seems fine then I can proceed to fix this on repository for review.