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