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;
Member Avatar for hfx642

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 for hfx642
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 for hfx642

My Bad!

Be a part of the DaniWeb community

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