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;
/

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

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.

Thanks, but unfortunately I still get the same error msg.

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