Re: trigger with cascade delete problem
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
Re: trigger with cascade delete problem
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
Re: trigger with cascade delete problem
Hi Tim, I still don't think I understand what's not working. Here's my crude understanding: 1) You have 3 tables: MAIN - This contains the information to be aggregated. GROUPING - This contains facts needed to group the information in MAIN AGGREGATES - This contains a pre-computed, grouped aggregation of MAIN, using the groups in GROUPING 2) A foreign key from MAIN to GROUPING defines the groups. It's unclear whether a similar foreign key links AGGREGATES to GROUPING. 3) When you delete a group from GROUPING, cascade actions delete the rows in the group from MAIN. And some triggers bring AGGREGATES back into agreement with the facts in MAIN and GROUPING. I understand why deleting a group means that you need to delete the corresponding aggregated result for that group from AGGREGATES. What I don't understand is why you need to do anything else to AGGREGATES. Is what's in AGGREGATES not simply the result of a grouped aggregation? I don't think I can speculate further without knowing more about how the contents of AGGREGATES are calculated. I understand that this may be the special sauce of your application which you don't want to share. Maybe you can share a simplified problem which shows why this aggregation is so tricky. Thanks, -Rick
Re: trigger with cascade delete problem
OK, so here's a simplified example. Didn't want to raise a JIRA as I'm really not sure this is a bug. Tim drop table MAIN; drop table DELETIONS; drop table CHANGED; create table MAIN ( MAIN_ID INT generated always as identity, GROUPING_ID INT, NAME VARCHAR(50) ); create table DELETIONS ( DELETIONS_ID int generated always as identity, MAIN_ID INT, NAME VARCHAR(50), WHENITHAPPENED TIMESTAMP ); create table CHANGED ( CHANGED_ID INT generated always as identity, MAIN_ID INT, NAME VARCHAR(50), WHENITHAPPENED TIMESTAMP ); insert into MAIN (GROUPING_ID, NAME) values (1, 'banana'), (1, 'lemon'), (1, 'grapefruit'), (2, 'apple'), (2, 'broccoli'), (3, 'strawberry'), (3, 'redcurrent'), (3, 'cherry'); select * from MAIN; -- this trigger records what has been deleted from MAIN CREATE TRIGGER trg_delete1 AFTER DELETE ON MAIN REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL INSERT into CHANGED (MAIN_ID, NAME, WHENITHAPPENED) SELECT MAIN_ID, NAME, CURRENT_TIMESTAMP FROM OLD; -- tries to record all rows with the same GROUPING_ID as any of the deleted rows CREATE TRIGGER trg_delete2 AFTER DELETE ON MAIN REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL INSERT into DELETIONS (MAIN_ID, NAME, WHENITHAPPENED) SELECT MAIN.MAIN_ID, MAIN.NAME, CURRENT_TIMESTAMP FROM MAIN, OLD WHERE MAIN.GROUPING_ID = OLD.GROUPING_ID; delete from MAIN where MAIN_ID = 1; select * from MAIN; -- banana deleted as expected select * from CHANGED; -- banana present as expected select * from DELETIONS; -- lemon and grapefruit present as expected, but no banana
Re: trigger with cascade delete problem
On 6/12/14 5:33 AM, Tim Dudgeon wrote: OK, so here's a simplified example. Didn't want to raise a JIRA as I'm really not sure this is a bug. Tim drop table MAIN; drop table DELETIONS; drop table CHANGED; create table MAIN ( MAIN_ID INT generated always as identity, GROUPING_ID INT, NAME VARCHAR(50) ); create table DELETIONS ( DELETIONS_ID int generated always as identity, MAIN_ID INT, NAME VARCHAR(50), WHENITHAPPENED TIMESTAMP ); create table CHANGED ( CHANGED_ID INT generated always as identity, MAIN_ID INT, NAME VARCHAR(50), WHENITHAPPENED TIMESTAMP ); insert into MAIN (GROUPING_ID, NAME) values (1, 'banana'), (1, 'lemon'), (1, 'grapefruit'), (2, 'apple'), (2, 'broccoli'), (3, 'strawberry'), (3, 'redcurrent'), (3, 'cherry'); select * from MAIN; -- this trigger records what has been deleted from MAIN CREATE TRIGGER trg_delete1 AFTER DELETE ON MAIN REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL INSERT into CHANGED (MAIN_ID, NAME, WHENITHAPPENED) SELECT MAIN_ID, NAME, CURRENT_TIMESTAMP FROM OLD; -- tries to record all rows with the same GROUPING_ID as any of the deleted rows CREATE TRIGGER trg_delete2 AFTER DELETE ON MAIN REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL INSERT into DELETIONS (MAIN_ID, NAME, WHENITHAPPENED) SELECT MAIN.MAIN_ID, MAIN.NAME, CURRENT_TIMESTAMP FROM MAIN, OLD WHERE MAIN.GROUPING_ID = OLD.GROUPING_ID; delete from MAIN where MAIN_ID = 1; select * from MAIN; -- banana deleted as expected select * from CHANGED; -- banana present as expected select * from DELETIONS; -- lemon and grapefruit present as expected, but no banana Hi Tim, This result looks correct to me. The trg_delete2 trigger fires AFTER the deletion and inside the same transaction. So it sees the state of the MAIN table AFTER the banana tuple was deleted. From its perspective, the banana table should not appear in the join. Or did I misunderstand your question? Hope this helps, -Rick
Re: trigger with cascade delete problem
Hi Rick, So much as I expected. Its probably not a bug, and working exactly as planned. So my question is how to get the behaviour I want! I can't use a BEFORE trigger as that doesn't let me delete rows (which is my real use case - the example is a gross simplification). I need to do a join to get the data I need, but the join depends on something that has just been deleted because this is an ON DELETE triger. Tim Hi Tim, This result looks correct to me. The trg_delete2 trigger fires AFTER the deletion and inside the same transaction. So it sees the state of the MAIN table AFTER the banana tuple was deleted. From its perspective, the banana table should not appear in the join. Or did I misunderstand your question? Hope this helps, -Rick
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
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
