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

asked 30 Aug, 17:02

Bill%20Aumen's gravatar image

Bill Aumen
2.1k344674
accept rate: 16%

edited 30 Aug, 17:37

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889

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 :)

(30 Aug, 17:55) Breck Carter

yep, that is exactly what is happening.

(30 Aug, 18:07) Bill Aumen
Replies hidden

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

(31 Aug, 05:58) Breck Carter

Yep, understood :) And answer on target, and appreciated

(31 Aug, 10:14) Bill Aumen
1

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

(31 Aug, 13:28) Volker Barth

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...

permanent link

answered 30 Aug, 17:15

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

converted 30 Aug, 17:51

Bill%20Aumen's gravatar image

Bill Aumen
2.1k344674

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!

(30 Aug, 17:51) Bill Aumen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×11
×1

question asked: 30 Aug, 17:02

question was seen: 96 times

last updated: 31 Aug, 13:28