Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    19

    Unanswered: SP2-0552: Bind variable "OLD" not declared.

    Could somebody please tell me why I am getting this error, and possibly how to fix it?
    Thank you
    Here is my code:
    CREATE OR REPLACE s_inventory_trig
    BEFORE INSERT OR UPDATE OR DELETE ON s_item
    FOR EACH ROW

    BEGIN
    IF NOT(product_id IN (SELECT product_id FROM s_inventory) THEN
    RAISE_APPLICATION_ERROR(-20202, 'S_Inventory row not found for product id '||product_id);
    END IF;

    IF INSERTING THEN
    IF amount_in_stock < 0 THEN
    raise_application_error (-20002, ' Insuficient amount for this transaction '|| :new.product_id);
    ELSE
    UPDATE s_inventory
    SET amount_in_stock = amount_in_stock - :NEW.quantity_shipped
    WHERE product_id = :NEW.product_id And warehouse_id = 101;
    END IF;

    ELSIF DELETING THEN
    Update s_inventory
    Set amount_in_stock = amount_in_stock + ld.quantity_shipped
    Where product_id = :OLD.product_id
    And warehouse_id = 101;

    ELSIF UPDATING THEN
    IF :NEW.quantity_shipped > :OLD.quantity_shipped THEN
    UPDATE s_inventory
    SET amount_in_stock = amount_in_stock - :NEW.quantity_shipped - :OLD.quantitiy_shipped;
    WHERE product_id = :NEW.product_id And warehouse_id = 101;
    END IF;
    IF amount_in_stock < 0 THEN
    raise_application_error (-20343, ' Insuficient amount for this transaction '|| :new.product_id);
    END IF;

    ELSE
    Update s_inventory
    Set amount_in_stock = amount_in_stock + :OLD.quantity_shipped - :NEW.quantity_shipped
    Where product_id = :OLD.product_id
    And warehouse_id = 101;
    END IF;

    END;
    /

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Use a tempvar in statement

    Hello,

    perhaps this works

    Orginal
    --------
    Update s_inventory
    Set amount_in_stock = amount_in_stock + ld.quantity_shipped
    Where product_id = :OLD.product_id
    And warehouse_id = 101;

    New
    -----

    nProductId := :OLD.product_id;
    Update s_inventory
    Set amount_in_stock = amount_in_stock + ld.quantity_shipped
    Where product_id = nProductId
    And warehouse_id = 101;


    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    The trigger fires 'BEFORE INSERT OR UPDATE OR DELETE ON s_item'.
    I'm pretty sure that :old isn't defined when inserting, and :new isn't defined when deleting.

  4. #4
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    19
    Thanks, but unfortunately I still get the same error msg.

  5. #5
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Hello

    ivon is completly correct ...
    You can not reference the ld variable in an insert trigger cause there is no old value for that record - cause it is new !!!

    You must split the trigger into a insert and update and delete trigger ...

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •