Oracle 11g Trigger Issue

Question

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?

Can you share the DDLs to your book and product tables please? — Mureinik
– Mureinik, Commented May 5, 2014 at 11:34
Thanks for tidying it up Eat A Peach. It messed up when posted. Any Suggestions? — user1893091
– user1893091, Commented May 5, 2014 at 11:45
How your PRODUCT and BOOK tables are connected? How to define for which book to change the value of IN_STOCK column? — Guneli
– Guneli, Commented May 5, 2014 at 11:47
Hi Mureinik, Heres the Tables CREATE TABLE PRODUCT ( PROD_ID NUMBER(, 0) NOT NULL , AMOUNT_REMAINING NUMBER(, 0) NOT NULL , PRICE NUMBER(9, 2) NOT NULL , IN_STOCK CHAR(1 BYTE) NOT NULL , CONSTRAINT PRODUCT_PK PRIMARY KEY — user1893091
– user1893091, Commented May 5, 2014 at 11:54
Sorry Mureinik, Heres the other one CREATE TABLE BOOK ( BOOK_ID NUMBER(, 0) NOT NULL , ISBN_ID VARCHAR2(100 BYTE) , TITLE VARCHAR2(100 BYTE) NOT NULL , PROD_ID NUMBER(, 0) NOT NULL , IN_STOCK CHAR(1 BYTE) , CONSTRAINT BOOK_PK PRIMARY KEY ( BOOK_ID ) — user1893091
– user1893091, Commented May 5, 2014 at 11:54

Guneli · Accepted Answer · 2014-05-05 12:11:23Z

1

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;

edited May 5, 2014 at 12:11

answered May 5, 2014 at 11:56

Guneli

1,7311 gold badge11 silver badges17 bronze badges

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

2 Comments

user1893091 Over a year ago

Thanks Guneli, That worked. I'll need to look up the code changes you made re: referencing and if statement to understand this. Thanks again.

Clockwork-Muse Over a year ago

You should probably have it check for negative stock, too (so, NEW.AMOUNT_REMAINING <= 0). Afterall, there's such a thing as shrink in a retail environment.

Collectives™ on Stack Overflow

Oracle 11g Trigger Issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related