Row in table 'apinp_invoice' was modified or deleted in BEFORE trigger error

[Former Member]

There is no doubt in my mind that I am missing something very basic here, but I am stumped:

I created a trigger to return an image to the original table before the row is deleted in a table later in the process. But when I delete the row in ISQL, I get Row in table 'apinp_invoice' was modified or deleted in BEFORE trigger.

The trigger:

 CREATE OR REPLACE TRIGGER "tbd_apinp_invoice" BEFORE DELETE ORDER 10 ON app_owner.apinp_invoice
   REFERENCING OLD AS old_data
   FOR EACH ROW
BEGIN
    //IF INVOICE SCAN IS PRESENT, MOVE IT BACK TO ORIGINAL SOURCE FILE IF THAT IS APPROPRIATE
    IF old_data.apinp_invoice_scan IS NOT NULL   THEN
        UPDATE edstpspayi_invoice_received
            SET edstpspayi_invoice_scan = old_data.apinp_invoice_scan
            WHERE   old_data.apven_id               = edstpspayi_invoice_received.apven_id      AND
                    old_data.apinp_invoice_number   = edstpspayi_invoice_received.apinp_invoice_number;
        IF sqlcode < 0   THEN
            RAISERROR 18000 '18000 tbd_apinp_invoice sql error ' || sqlcode;
        END IF;
    END IF;
END;

The list of triggers on this table (got error uploading jpg, so here is text):

ta_apinp_invoice   Watcom-SQL   Insert, Update    After
tb_apinp_invoice   Watcom-SQL   Insert, Update    Before
tbd_apinp_invoice  Watcom-SQL   Delete            Before

[Breck_Carter]

Is the error coming from a trigger on some other table, such as edstpspayi_invoice_received?

( as in Number 41 here: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers 🙂

[Former Member]

yep, that is exactly what is happening.

[Breck_Carter]

FWIW your comment "Of course!!" was not visible when I posted my comment "Is the error coming from".

[Former Member]

Yep, understood 🙂 And answer on target, and appreciated

[VolkerBarth]

Bill, FYI, I deleted the duplicate question via means of moderator super powers:)

[VolkerBarth · Accepted solution]

What does the before update trigger do? Are there triggers on the other table, too?

I'd add MESSAGE statemens to follow the order of execution in case you suspect cyclic effects...

[Former Member · Accepted solution]

Are there triggers on the other table, too?

Of course!! The trigger that puts the scan back on the apinp_invoice table...

Duh!

Thanks Volker!