Xtykutl

I have two tables which are linked to each other using PK/FK.
1st table row may lead to (0 to *) records in the 2nd table.
Like customer > bank account.

So what I want is when customer's state changes to 2(inactive) all his accounts should be closed (set to inactive as well).

I don't know what is the best way to do it, but I am trying to solve it using COMPOUND TRIGGER and it gives me an error PLS-00642: local collection types not allowed in SQL statements.

The problem I faced for now is with using collections. I want to update the state of accounts for all affected customers.
Any alternative solutions are highly appreciated.

I can try to describe my idea in JS code:

// declaration

var collection_of_records;

// before statement block

collection_of_records = ;

// end of before statement

// before update for each row block

for (row in table1) {

    if (row.state_type_1 == 2) {

        collection_of_records.push(row.id);

    }

}

// end of before update

// after statement block

table2

.filter((row) => {

    return collection_of_records.contains(row.parent_id);

})

.forEach((row) => {

    var idx = table2.indexOf(row);

    row.state_type_2 = 2;

    table2[idx] = row;

});

// end of after statement block

Here is my PL/SQL code:

CREATE OR REPLACE TRIGGER T186121_T_UPDT_PKOHT_MT_AKTVN

    FOR UPDATE OF PARKLA_SL_KOOD ON T186121_PARKLA

    COMPOUND TRIGGER

        TYPE P_KOOD IS TABLE OF T186121_PARKLA.PARKLA_KOOD%TYPE;

        PARKLAD P_KOOD;

    BEFORE STATEMENT IS

    BEGIN

        PARKLAD := P_KOOD(); -- init

    END BEFORE STATEMENT;

    BEFORE EACH ROW IS

    BEGIN

        IF (:NEW.PARKLA_SL_KOOD = 2) THEN

            PARKLAD.EXTEND; -- expend by 1 line

            PARKLAD(PARKLAD.LAST) := :OLD.PARKLA_KOOD; -- add ID to collection

            DBMS_OUTPUT.PUT_LINE('FIRED FOR ' || :OLD.PARKLA_KOOD);

            DBMS_OUTPUT.PUT_LINE('ARRAY LENGTH CURRENTLY IS ' || PARKLAD.COUNT);

        END IF;

    END BEFORE EACH ROW;

    AFTER STATEMENT IS

    BEGIN

        DBMS_OUTPUT.PUT_LINE('ARRAY LENGTH IS ' || PARKLAD.COUNT);

        UPDATE T186121_PKOHT SET PKOHA_SL_KOOD = 2

        WHERE PARKLA_KOOD MEMBER OF PARKLAD; -- here comes the problem.

    END AFTER STATEMENT;

END T186121_T_UPDT_PKOHT_MT_AKTVN;