Request to write and explain the operation of a trigger that will first update the parent table TAB1
(update in the code) and then TAB2
. I get this error:
ERROR: SQL ERROR [23504]: THE PARENT KEY IN A PARENT ROW OF RELATIONSHIP "TAB2.TAB2_FK" CANNOT BE UPDATED.. SQLCODE=-531, SQLSTATE=23504, DRIVER=4.25.13
Code:
CREATE TABLE TAB1
(
ID INTEGER PRIMARY KEY NOT NULL,
SOMETHING VARCHAR(100) NULL
);
CREATE TABLE TAB2
(
ID INTEGER PRIMARY KEY NOT NULL,
TAB1_ID INTEGER NOT NULL
);
ALTER TABLE TAB2
ADD CONSTRAINT TAB2_FK
FOREIGN KEY (TAB1_ID) REFERENCES TAB1(ID);
INSERT INTO TAB1 (ID, SOMETHING) VALUES (1, 'XYZ');
INSERT INTO TAB1 (ID, SOMETHING) VALUES (2, 'ABC');
INSERT INTO TAB2 (ID, TAB1_ID) VALUES (1, 1);
UPDATE TAB1
SET ID = 5
WHERE ID = 1;
DB2 doesn't support UPDATE CASCADE in referential constraints as some other vendors.
You may try a workaround with a view and INSTEAD OF UPDATE
trigger on it. You use namely this view in such UPDATE PK statements, not the base table.
Notice, that it's not intended for cases when a new value may be in a list of old values like UPDATE TAB1_V SET ID = ID + 1
--#SET TERMINATOR @
CREATE VIEW TAB1_V AS SELECT * FROM TAB1
@
CREATE OR REPLACE TRIGGER TAB1_V_IU
INSTEAD OF UPDATE
ON TAB1_V
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
BEGIN ATOMIC
IF (n.ID <> o.ID) THEN
INSERT INTO TAB1(ID, SOMETHING) VALUES (n.ID, n.SOMETHING);
UPDATE TAB2 SET TAB1_ID = n.ID WHERE TAB1_ID = o.ID;
DELETE FROM TAB1 WHERE ID = o.ID;
ELSE
UPDATE TAB1 SET SOMETHING = n.SOMETHING WHERE ID = n.ID;
END IF;
END
@
UPDATE TAB1_V
SET ID = 5
WHERE ID = 1
@