CODEWITHSUNDEEP
sqloracle-databasetriggers
Blued00d
Blued00d

Reputation: 170

ORACLE SQL Creating Update Trigger

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!

Upvotes: 0

Views: 89

Answers (2)

ruudvan
ruudvan

Reputation: 1371

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.

Upvotes: 2

Erik Blessman
Erik Blessman

Reputation: 693

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

:new.Quantity

Upvotes: 1

Related Questions