[bbk]

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

[alligatorsql.com]

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

[ivon]

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.

[bbk]

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

[alligatorsql.com]

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