Derby ERROR 54038: Maximum depth of nested triggers was exceeded

Question

I am trying to fire a trigger which sets owner_notif_sta to null after an update or insertion to owner_notif_sta if owner_id is null. Both owner_id and owner_notif_sta are from the same table.

The trigger:

CREATE TRIGGER check_owner
AFTER UPDATE OF owner_notif_sta ON risk
REFERENCING OLD AS EXISTING
FOR EACH ROW MODE DB2SQL
    UPDATE risk SET owner_notif_sta = NULL
    WHERE owner_id IS NULL;

The update I tried to fire:

UPDATE RISK SET owner_notif_sta = 'helloo' WHERE risk_id = 'ICT123';

and owner_id was null (not populated when i fired the update)

and I received the following error: ERROR 54038: Maximum depth of nested triggers was exceeded.

Thanks in advance

Answer 1

What about changing the UPDATE statement slightly to exclude rows that already have owner_notif_sta as null:

CREATE TRIGGER check_owner
AFTER UPDATE OF owner_notif_sta ON risk
REFERENCING OLD AS EXISTING
FOR EACH ROW MODE DB2SQL
    UPDATE risk 
       SET owner_notif_sta = NULL
     WHERE owner_id IS NULL
       AND owner_notif_sta IS NOT NULL;