db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: trigger with cascade delete problem
Date Thu, 12 Jun 2014 12:33:38 GMT
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






Mime
View raw message