Creating a DELETE trigger in Db2

I’m trying to create an DELETE trigger to archive old data. I have a sample fiddle at https://dbfiddle.uk/H_7B93WK .

I have two source tables: parents and children with a foreign key from children to parents:

CREATE TABLE parents (
    id int NOT NULL PRIMARY KEY,
    name varchar(255)
);
CREATE TABLE children (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parentid int REFERENCES parents(id) ON DELETE CASCADE,
    data varchar(255)
)

The idea is that when a parent row is deleted I want to concatenate the children data and copy the data into an archive table:

CREATE TABLE archive(
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parentid int,
    parentname varchar(255),
    items varchar(255), --  concatenated child data
    archived date
);

I’ve managed to do this in several other dialects of SQL (PostgreSQL, MSSQL, Oracle, MariaDB) but I’m grinding to a halt with Db2.

Here is what I thought might do the job:

CREATE TRIGGER test_trigger
INSTEAD OF DELETE ON parents
REFERENCING OLD AS oldrow FOR EACH ROW
--  BEGIN ATOMIC
    INSERT INTO archive(parentid, parentname, items, archived)
    WITH cte(id, name, child_items) AS (
        SELECT p.id, p.name, listagg(c.data,';')
        FROM parents AS p JOIN children AS c ON c.parentid=p.id
        WHERE p.id=oldrow.id
        GROUP BY p.id, p.name
    )
    SELECT id, name, child_items, current_date
    FROM cte;
--  END

I’m using an INSTEAD OF DELETE trigger because I want to get data from both tables before they’re deleted, and I haven’t yet included the code to delete the data when I’ve finished.

I’m getting errors which I don’t understand. With the BEGIN and END commented out, I get:

SQL Error [42809]: The statement references an object that identifies an unexpected object type. Object: "DB2INST1.PARENTS". Object type: "TABLE". Expected object type: "UNTYPED VIEW".. SQLCODE=-159, SQLSTATE=42809, DRIVER=4.33.31

whatever that means.

With the BEGIN and END enabled I get:

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

whatever that means.

Maybe I should use a different trigger.

Any ideas on how I can finish this?

Using Db2 v12 LUW in a Docker container.

INSTEAD OF triggers are used on views in DB2, not on tables.
You may use a compiled (BEGIN ... END, not BEGIN ATOMIC ... END) compound statement in a BEFORE DELETE trigger on the parent table to get the result.
Notice, that you must use different statement terminator (@ here) in your script instead of the default one (;), when the script has a compound statement.

--#SET TERMINATOR @

CREATE TABLE parents (
    id int NOT NULL PRIMARY KEY,
    name varchar(255)
)@

CREATE TABLE children (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parentid int REFERENCES parents(id) ON DELETE CASCADE,
    data varchar(255)
)
@

CREATE TABLE archive(
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parentid int,
    parentname varchar(255),
    items varchar(255), --  concatenated child data
    archived date
)
@

CREATE TRIGGER test_trigger
BEFORE DELETE ON parents
REFERENCING OLD AS oldrow FOR EACH ROW
BEGIN 
    INSERT INTO archive(parentid, parentname, items, archived)
    WITH cte(id, name, child_items) AS (
        SELECT p.id, p.name, listagg(c.data,';')
        FROM parents AS p JOIN children AS c ON c.parentid=p.id
        WHERE p.id=oldrow.id
        GROUP BY p.id, p.name
    )
    SELECT id, name, child_items, current_date
    FROM cte;
END
@

INSERT INTO parents (id, name) VALUES (1, 'name1')@
INSERT INTO children (parentid, data) VALUES (1, 'data1'), (1, 'data2')@
DELETE from parents@
SELECT * FROM archive@

The result of the last query is:

fiddle