Reputation: 9
I'm a newbie to database and trying to create a trigger which will change a char from "Y" to "N" if another table's tuple is set to 0. I have it working somewhat but it is changing all tuples instead of the single one I want. Here is the trigger and code. Any suggestions gratefully received.
create or replace TRIGGER CHANGE_STOCK_FLAG
AFTER UPDATE OF AMOUNT_REMAINING ON PRODUCT
FOR EACH ROW
BEGIN
UPDATE BOOK
SET IN_STOCK = 'N';
END;
Update statement:
UPDATE PRODUCT
SET AMOUNT_REMAINING = 0
WHERE PROD_ID = 5001;
The trigger compiled OK and on update above resets IN_STOCK to "N" on all tuples in the Book TABLE. Is there a where clause or something I can use?
Upvotes: 0
Views: 58
Reputation: 1731
Try this:
CREATE OR REPLACE TRIGGER CHANGE_STOCK_FLAG
AFTER UPDATE OF AMOUNT_REMAINING ON PRODUCT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (:NEW.AMOUNT_REMAINING=0) THEN
UPDATE BOOK SET IN_STOCK = 'N' WHERE PROD_ID=:NEW.PROD_ID;
END IF;
END;
Upvotes: 1