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 |
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...
Of course!! The trigger that puts the scan back on the apinp_invoice table... Duh! Thanks Volker!
(30 Aug '17, 17:51)
Bill Aumen
|
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 :)
yep, that is exactly what is happening.
FWIW your comment "Of course!!" was not visible when I posted my comment "Is the error coming from".
Yep, understood :) And answer on target, and appreciated
Bill, FYI, I deleted the duplicate question via means of moderator super powers:)