On delete cascade causes child table trigger to not work
We have tables T1 (parent table) and T2 (child table), where T2 has an FK back to T1. T2 also has an On Delete Trigger Trig1 to capture some auditing data.
Trig1 is a For Delete compound trigger with:
- Before Statement section (capture context info)
- After Row section (capture old values in a collection)
- After Statement section (insert all old values into a logging table by looping through the collection)
In sqlplus, if I delete a row from T2, the trigger works correctly. However, if T2 records are deleted from cascading deletes by deleting records from T1, the trigger does not work - the After Statement section shows the collection count as 0 as if the collection was reinitialized or something.