Major Description
When an after delete trigger which inserts into a table is created on a table that has a foreign key that references a primary key and uses the on delete cascade constraint, nulls are inserted into the table by the trigger.
The SQL below shows that the cascade delete works correctly:
ij> CREATE TABLE TABLE1 ( X INT PRIMARY KEY );
0 rows inserted/updated/deleted
ij> CREATE TABLE TABLE1_DELETIONS ( X INT );
0 rows inserted/updated/deleted
ij> CREATE TABLE TABLE2 (
Y INT,
CONSTRAINT Y_AND_X FOREIGN KEY(Y) REFERENCES TABLE1(X) ON DELETE CASCADE
);
0 rows inserted/updated/deleted
ij> CREATE TABLE TABLE2_DELETIONS ( Y INT );
0 rows inserted/updated/deleted
ij> INSERT INTO TABLE1 VALUES (0);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE2 VALUES (0);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE1 VALUES (1);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE2 VALUES (1);
1 row inserted/updated/deleted
ij> select * from table1;
X
-----------
0
1
2 rows selected
ij> select * from table2;
Y
-----------
0
1
2 rows selected
ij> DELETE FROM TABLE1;
2 rows inserted/updated/deleted
ij> select * from table1;
X
-----------
0 rows selected
ij> select * from table2;
Y
-----------
0 rows selected
Now insert the rows again, create the triggers, delete the rows from the primary key table, verify the cascade delete worked and observe the values in the tables used by the triggers:
ij> INSERT INTO TABLE1 VALUES(0);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE2 VALUES(0);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE1 VALUES(1);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE2 VALUES(1);
1 row inserted/updated/deleted
ij> CREATE TRIGGER TRIGGER1
AFTER DELETE ON TABLE1
REFERENCING OLD AS OLD_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO TABLE1_DELETIONS VALUES (OLD_ROW.X);
0 rows inserted/updated/deleted
ij> CREATE TRIGGER TRIGGER2
AFTER DELETE ON TABLE2
REFERENCING OLD AS OLD_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO TABLE2_DELETIONS VALUES (OLD_ROW.Y);
0 rows inserted/updated/deleted
ij> DELETE FROM TABLE1;
2 rows inserted/updated/deleted
ij> select * from TABLE1;
X
-----------
0 rows selected
ij> select * from TABLE2;
Y
-----------
0 rows selected
ij> SELECT * FROM TABLE1_DELETIONS;
X
-----------
0
1
2 rows selected
ij> SELECT * FROM TABLE2_DELETIONS;
Y
-----------
NULL
NULL
The TABLE2_DELETIONS table contains NULLs instead of the correct values which should be 0 and 1.