CODEWITHSUNDEEP
sqloracle-databasetriggersoracle11g
user1893091
user1893091

Reputation: 9

Oracle 11g Trigger Issue

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

Answers (1)

Guneli
Guneli

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

Related Questions