db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: trigger with cascade delete problem
Date Wed, 11 Jun 2014 12:41:10 GMT
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


Mime
View raw message