Create a script named *just_lee_trg.sql* that contains the PL/SQL code to create a trigger named *books_qty_on_hand_trg*. The trigger should be set up to fire whenever the quantity on hand for a book has been updated. When the quantity on hand becomes zero, it should call the *insert_reorder* procedure and pass the ISBN for the book.

That is my code.. I m not sure where i m doing wrong
ERRORS

9/8 PLS-00103: Encountered the symbol "INSERT_REORDE_PP" when expecti ng one of the following: := . ( @ % ; The symbol ":=" was sub stituted for "INSERT_REORDE_PP" to continue.
10/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder no t rem <an exponent (**)> <> or != or ~= >= <= <> and or like L IKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ Th e symbol ";" was substituted for "END" to continue.

CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity  ON  books 
FOR EACH ROW
DECLARE
 CURSOR book_cur IS
   SELECT isbn,on_hand_quantity
   FROM books
   WHERE isbn = :NEW.isbn;
BEGIN
 FOR book_rec IN book_cur LOOP
 IF book_rec.on_hand_quantity = 0 THEN
 CALL  insert_reorde_pp (:NEW.isbn)
END IF;
END LOOP;
END;

Recommended Answers

Well... Since ISBN should be a unique number, you don't need the Cursor, or the For Loop.

Also... Insert_ReOrde_PP must be a stored procedure in the DB, don't "CALL" it.
You just need the name of the procedure and it's parameter.

Jump to Post
CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN 
   IF :New.on_hand_quantity = 0 THEN 
      insert_reorde_pp (:NEW.isbn)
   END IF;
END;
Jump to Post

All 6 Replies

Member Avatar

Well... Since ISBN should be a unique number, you don't need the Cursor, or the For Loop.

Also... Insert_ReOrde_PP must be a stored procedure in the DB, don't "CALL" it.
You just need the name of the procedure and it's parameter.

Can you just give example code please, I dont get it
Thanks

Member Avatar
CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN 
   IF :New.on_hand_quantity = 0 THEN 
      insert_reorde_pp (:NEW.isbn)
   END IF;
END;
CREATE OR REPLACE TRIGGER books_qty_on_hand_trg
AFTER UPDATE OF on_hand_quantity ON books
FOR EACH ROW
BEGIN 
   IF :New.on_hand_quantity = 0 THEN 
      insert_reorde_pp (:NEW.isbn)
   END IF;
END;

After run the code
I m getting this error
PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.

add a semicolon at the end of procedure call.

insert_reorde_pp (:NEW.isbn);

Member Avatar

My Bad!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.