ORACLE SQL Creating Update Trigger

Question

Hey all so I am trying to create a trigger that will Update values in a table when they fall below a certain value.

For example say I have table Inventory:

Item | Quantity
-----|---------
A    |      400
B    |      160
C    |     1200
D    |      105

I want to make a trigger that will add a random value(100 - 200) if Quantity of an item should ever drop below 100.

This is what I have come up with:

CREATE OR REPLACE TRIGGER QuantityTrigger
AFTER
UPDATE ON INVENTORY FOR EACH ROW
BEGIN
 IF :QUANTITY < 100 THEN
   UPDATE INVENTORY
   SET QUANTITY = QUANTITY + dbms_random.value(100,200);
 END IF;
END;

However this gives me error: Error(2,9): PLS-00049: bad bind variable 'QUANTITY'

What am I doing wrong? Any advice is appreciated. Thanks!

remove the : in front of QUANTITY in the IF clause, oracle thinks it as an input (bind) variable — Shankar
– Shankar, Commented Mar 3, 2015 at 15:59
Made the change now it gives me the error: Error(2,9): PLS-00201: identifier 'QUANTITY' must be declared — Blued00d
– Blued00d, Commented Mar 3, 2015 at 16:00
Aramillo's change allowed it to compile successfully. Now to test it. — Blued00d
– Blued00d, Commented Mar 3, 2015 at 16:02

ruudvan · Accepted Answer · 2015-03-03 16:14:38Z

2

Syntax Error: Change :QtyOnHand to :new.QtyOnHand.

Logic error: Instead of writing an after trigger, change it to a before trigger and only update the column value without doing any UPDATE.

CREATE OR REPLACE TRIGGER QuantityTrigger
BEFORE
UPDATE ON QITEM FOR EACH ROW
BEGIN
 IF :new.QtyOnHand < 100 THEN
   :new.QtyOnHand := :new.QtyOnHand + dbms_random.value(100,200);
 END IF;
END;
/

I would however investigate what program inserts values in this table less than 100 and fix that. Using a trigger is a hack by which you are trying to mask a problem's symptoms rather than curing it.

Just use the same logic in the insert statement wherever you suspect values less than 100 are being inserted.

edited Mar 3, 2015 at 16:14

answered Mar 3, 2015 at 16:02

ruudvan

1,3816 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Blued00d Over a year ago

This code does not work, there is an error with :new.QtyOnHand = :new.QtyOnHand it say that it expects '('

ruudvan Over a year ago

Sorry fixed. The assignment operator is := not =.

Erik Blessman · Accepted Answer · 2015-03-03 16:02:14Z

1

I believe that the value you are looking for is in:

:new.Quantity

answered Mar 3, 2015 at 16:02

Erik Blessman

6934 silver badges11 bronze badges

Collectives™ on Stack Overflow

ORACLE SQL Creating Update Trigger

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related