Re: trigger with cascade delete problem

[Tim Dudgeon]

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