How to create a trigger that checks when one value is updated for defined values still being null and if not updates a table

Question

How can I create a trigger that is triggered by an update and then checks if there are certain values in the updated table and another table still null. If not it should change a value in a third table to sysdate.

My code:

    CREATE OR REPLACE TRIGGER TRG_UPDATE_VALUE
        BEFORE UPDATE OF column1
        ON table1
    REFERENCING OLD AS OLD NEW AS NEW
        for each row
    DECLARE
        value_one_null int;
        value_two_null int;

    BEGIN
        SELECT COUNT(table1_id)
        INTO value_one_null
        FROM table1
        WHERE column1 IS NULL
        AND column2 = :NEW.column2;

        SELECT COUNT(table2_id)
        INTO value_two_null
        FROM table2
        WHERE column1 IS NULL
        AND column2 = :NEW.column2;

        IF (value_one_null = 0 AND value_two_null = 0)
            THEN
            UPDATE table3 SET table3.column1 = sysdate 
                WHERE table3_id = :NEW.column2;
        END IF;
    END;

Compiling the trigger works. But if I try to update table 1 it errors ORA-04091, ORA-06512, ORA-04088

I am a total beginner in PL/SQL so it would be amazing if someone could help me to fix this error.

@Viorel thank you for the answer. Not I can update the table but if I place the commit after end if, a deadlock happens when value should be set to sysdate. if I don't put it there the value simply does not change... do you have any idea how to fix this? — Rollaway
Sample data and desired results would help me understand what you are trying to do. You may need a different approach to solving this problem, because of the mutating table issue. — Gordon Linoff

PavelX PavelX · Accepted Answer · 2020-02-15T16:51:10

The main problem is that you are trying to read rows in row-level trigger from the target table (a table which is the trigger is associated to). This operaration is prohibited by RDMS.

The general approach to implement the functionality is to use a common memory by COMPOUND triggers or packages and achieve the goal in a number of steps.

Please read initial topic of Tom Kyte about mutating tables and triggers to learn the topic.

Here is one of the hundreds of explanations:

Mutating table/trigger error and how to resolve it

How to create a trigger that checks when one value is updated for defined values still being null and if not updates a table

1 Answers