trigger with cascade delete problem
I've encountered a tricky problem that I can't see a solution to at present. Let me describe. I have 3 tables: MAIN - the main data table with results in. GROUPING - a table that is related to MAIN, with MAIM having a FK constraint to GROUPING AGGREGATES - a table that contains aggregated information from MAIN, in part grouped by the info in GROUPING. I'm filling the data in AGGREGATES using triggers on MAIN that firstly delete the old aggregate value and then inserts a new aggregate (e.g. 2 after insert/update/delete for each statement triggers). Mostly its working. When I delete a row in MAIN the row in AGGREGATES gets deleted and then inserted again with the new aggregate. But when I delete a row from GROUPING and the cascade delete causes the corresponding rows in MAIN to be deleted it does not work. I'm pretty sure this is because part of the selection criteria for the rows to delete involves a join to GROUPING, and the rows to join to have just been blown away by the delete operation, so nothing in AGGREGATES gets deleted. Is there solution to this? Tim
Re: trigger with cascade delete problem
On 6/11/14 2:40 AM, Tim Dudgeon wrote: I've encountered a tricky problem that I can't see a solution to at present. Let me describe. I have 3 tables: MAIN - the main data table with results in. GROUPING - a table that is related to MAIN, with MAIM having a FK constraint to GROUPING AGGREGATES - a table that contains aggregated information from MAIN, in part grouped by the info in GROUPING. I'm filling the data in AGGREGATES using triggers on MAIN that firstly delete the old aggregate value and then inserts a new aggregate (e.g. 2 after insert/update/delete for each statement triggers). Mostly its working. When I delete a row in MAIN the row in AGGREGATES gets deleted and then inserted again with the new aggregate. But when I delete a row from GROUPING and the cascade delete causes the corresponding rows in MAIN to be deleted it does not work. I'm pretty sure this is because part of the selection criteria for the rows to delete involves a join to GROUPING, and the rows to join to have just been blown away by the delete operation, so nothing in AGGREGATES gets deleted. Is there solution to this? Tim Hi Tim, I'm sure I don't understand the complexity of your problem. But it sounds as though there may be tuples in MAIN with null foreign keys and somehow these are involved in the aggregation function. Often the solution to a missing join problem is to use a left join rather than an inner join. Hope this helps, -Rick
ApacheCon CFP closes June 25
Dear Apache DDLUtils, Torque, JDO and Derby enthusiasts, As you may be aware, ApacheCon will be held this year in Budapest, on November 17-23. (See http://apachecon.eu for more info.) The Call For Papers for that conference is still open, but will be closing soon. We need you talk proposals, to represent the DB project at ApacheCon. We need all kinds of talks - deep technical talks, hands-on tutorials, introductions for beginners, or case studies about the awesome stuff you're doing with DDLUtils, Torque, JDO and Derby. Please consider submitting a proposal, at http://events.linuxfoundation.org//events/apachecon-europe/program/cfp Thanks! Myrna van Lunteren DB Project chair
Re: trigger with cascade delete problem
Tim, if you could produce a JIRA with a repro for this scenario, we could take a look at the generated code for the query. Thanks, Dag On 11. juni 2014 16:48, Tim Dudgeon wrote: Hi Tim, I'm sure I don't understand the complexity of your problem. But it sounds as though there may be tuples in MAIN with null foreign keys and somehow these are involved in the aggregation function. Often the solution to a missing join problem is to use a left join rather than an inner join. Hope this helps, -Rick No, the data is good. No missing keys. Inner join gets everything. I *think* its because is a cascade delete, Should the cascade delete and the firing of the trigger all happen in the same transaction and see the deleted row in GROUPING that caused the cascasde deltete? It looks like it doesn't Tim
Re: trigger with cascade delete problem
Tim, if you could produce a JITA with a repro for this scenario, we could take a look at the generated code for the query. Thanks, Dag On 11. juni 2014 16:48, Tim Dudgeon wrote: Hi Tim, I'm sure I don't understand the complexity of your problem. But it sounds as though there may be tuples in MAIN with null foreign keys and somehow these are involved in the aggregation function. Often the solution to a missing join problem is to use a left join rather than an inner join. Hope this helps, -Rick No, the data is good. No missing keys. Inner join gets everything. I *think* its because is a cascade delete, Should the cascade delete and the firing of the trigger all happen in the same transaction and see the deleted row in GROUPING that caused the cascasde deltete? It looks like it doesn't Tim
