SQL Recursion error

Question

I have the following trigger written, and am getting an error i don't understand.... the error I'm getting is :

Error report - SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded

how would this be?

i didn't intend to do any recursion, is it because my select statement is to vague? create or replace TRIGGER INVENTORY_AVAIL Before INSERT ON TORDER FOR EACH ROW

    DECLARE
       v_quantity_diff number;
       v_onhand_quantity number;

    BEGIN
       -- TRIGGR ON THIS...
       --INSERT INTO ORDER
       --( FK_ORDER_NO, FK_PROD_ID, QUANITY , COMPLETE_STATUS)
       --VALUES
       --( :NEW.FK_ORDER_NO, :NEW.FK_PROD_ID, :NEW.QUANITY , :NEW.COMPLETE_STATUS);
       SELECT INVENTORY_ONHAND INTO v_onhand_quantity
       FROM INVENTORY 
       WHERE :NEW.fk_prod_id = INVENTORY.FK_PROD_ID;

      IF( (v_onhand_quantity - :NEW.QUANTITY) >= 0)
      THEN
         INSERT INTO ORDER ( FK_ORDER_NO, FK_PROD_ID, QUANTITY, COMPLETE_STATUS)
          VALUES ( :NEW.FK_ORDER_NO, :NEW.FK_PROD_ID, :NEW.QUANTITY , :NEW.COMPLETE_STATUS);

      ELSE
      raise_application_error (-20001,'ERROR: QUANTITY ' || :NEW.QUANTITY 
           || ' EXCEEDS INVENTORY ONHAND [' || TO_CHAR(v_onhand_quantity) || ']' ); 
      END IF;

    END;

Is there another trigger on table ORDER that inserts into THC_ORDER ? — Charles Bretana
– Charles Bretana, Commented May 10, 2014 at 23:32
no, my mistake, just a table name change. thc is no longer--its just order now. — StillLearningToCode
– StillLearningToCode, Commented May 10, 2014 at 23:37

Charles Bretana · Accepted Answer · 2014-05-10 23:54:50Z

Then it's clear, you have a trigger on table Order Inserts that inserts into table Order/

i.e.,

Your code inserts one or more rows into Table Order, firing trigger.
trigger executes, and examines on_hand quantity, if sufficient, it
inserts into table ORDER, firing trigger,
trigger executes, and examines on_hand quantity, if sufficient, it
inserts into table ORDER, firing trigger,
trigger executes, and examines on_hand quantity, if sufficient, it
inserts into table ORDER, firing trigger,
trigger executes, and examines on_hand quantity, if sufficient, it
inserts into table ORDER, firing trigger, etc..

Your application code should have already done the Insert when the trigger runs... If there are sufficient items in inventory you simply need to let the transaction be committed, you do not need to perform the insert again.

If, otoh, there are not sufficient items in inventory, then you need to rollback the transaction in the trigger (as well as any other operations initiated in the trigger.

here is kinda the way it ought to be written:

DECLARE
   v_quantity_diff number;
   v_onhand_quantity number;

BEGIN
   -- TRIGGR ON THIS...
   --INSERT INTO ORDER
   --( FK_ORDER_NO, FK_PROD_ID, QUANITY , COMPLETE_STATUS)
   --VALUES
   --( :NEW.FK_ORDER_NO, :NEW.FK_PROD_ID, :NEW.QUANITY , :NEW.COMPLETE_STATUS);
   SELECT INVENTORY_ONHAND INTO v_onhand_quantity
   FROM INVENTORY 
   WHERE :NEW.fk_prod_id = INVENTORY.FK_PROD_ID;

  IF( (:NEW.QUANTITY > v_onhand_quantity) 
  THEN Begin
      Rollback Transaction
      raise_application_error (-20001,'ERROR: QUANTITY ' || :NEW.QUANTITY 
       || ' EXCEEDS INVENTORY ONHAND [' || TO_CHAR(v_onhand_quantity) || ']' ); 
     End
  END IF;

END;

Collectives™ on Stack Overflow

SQL Recursion error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related