Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Message-ID: <539EE773.7020807@oracle.com>
Date: Mon, 16 Jun 2014 05:47:47 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: trigger with cascade delete problem
References: <ln985v$c97$1@ger.gmane.org> <53984E66.503@oracle.com>
 <ln9q7g$jj0$1@ger.gmane.org> <5398EBE8.7050606@oracle.com>
 <lnc6mv$ssg$1@ger.gmane.org> <5399C1AE.6040109@oracle.com>
 <lnco4s$umu$1@ger.gmane.org> <539AF5C5.90808@oracle.com>
 <lnm8ki$ia$1@ger.gmane.org>
In-Reply-To: <lnm8ki$ia$1@ger.gmane.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 6/16/14 1:07 AM, Tim Dudgeon wrote:
> On 13/06/2014 13:59, Rick Hillegas wrote:
>
>> Maybe
>> you can share a simplified problem which shows why this aggregation is
>> so tricky.
>>
>> Thanks,
>> -Rick
>>
>>
>
> Hi Rick
>
> Thanks for your patience!
> I've tried to create a minimal version that describes the problem:
>
> create table XXX (
>     XXX_ID int primary key generated always as identity
> );
>
> create table GROUPING (
>     GROUPING_ID int primary key generated always as identity,
>     XXX_ID INT,
>     constraint GROUPING2XXX foreign key (XXX_ID) references XXX 
> (XXX_ID) on delete cascade
> );
>
> create table MAIN (
>     MAIN_ID INT generated always as identity,
>     GROUPING_ID INT,
>     YYY_ID INT,
>     NAME VARCHAR(50),
>     constraint MAIN2GROUPING foreign key (GROUPING_ID) references 
> GROUPING (GROUPING_ID) on delete cascade
> );
>
> create table AGGREGATES (
>     AGGREGATES_ID int generated always as identity,
>     XXX_ID INT,
>     YYY_ID INT,
>     NAME VARCHAR(50)
> );
>
>
>
> The key parts is that AGGREGATES contains data that is grouped by 
> YYY_ID (from MAIN) and XXX_ID (from XXX/GROUPING).
> So when a row from MAIN is deleted its CORRESPONDING row in AGGREGATE 
> needs updating, which I'm doing by doing a delete followed by an INSERT.
> The row to delete is the one defined by the corresponding YYY_ID and 
> XXX_ID values. And to get the corresponding XXX_ID values I need to 
> join back to the GROUPING table. But in the case of a cascade delete 
> caused by deletion of a row in GROUPING the row I need to join on is 
> no longer present, as its just been deleted.
>
> And I can't do a cascade delete from GROUPING to AGGREGATES as that 
> would delete all AGGREGATES with the corresponding XXX_ID, not just 
> those with the correct XXX_IDs.
>
> I hope this is clearer!
>
> Tim
>
>
>
>
>
Hi Tim,

I wonder if you could use a BEFORE trigger which buffers some result in 
memory for later use by your AFTER trigger. Something like this:

create procedure PRECOMPUTE( grouping_id int )
language java parameter style java reads sql data
external name 'zz.precompute';

-- precompute the result
CREATE TRIGGER trg_beforedelete1 NO CASCADE BEFORE DELETE ON MAIN
REFERENCING OLD ROW AS OLD  FOR EACH ROW MODE DB2SQL
CALL precompute( OLD.GROUPING_ID );

The deleted rows could even be buffered up in memory by a BEFORE trigger 
and then they could be wrapped by a table function for use in the join 
performed by the AFTER trigger.

Disclaimer: I haven't tried this experiment. I'm just waving my hands.

Hope this helps,
-Rick