0

I have a list of products in a table, I wanted to create a trigger that reads the stock in hand for a particular product and then if the stock is less than 5 display a message. the concept is pretty simple.

Here is what i have so far.

SET SERVEROUTPUT ON
SET ECHO ON

CREATE OR REPLACE TRIGGER TRG_REORDERSTOCK
AFTER INSERT OR UPDATE OF S_QUANTITY ON STOCK_INVENTORY
FOR EACH ROW
BEGIN
IF :OLD.S_QUANTITY <= 10 THEN
     DBMS_OUTPUT.PUT_LINE ('Warning: ----- product with ID ('  || :OLD.BR_ID || ') has (' || :NEW.S_QUANTITY || ') units remaining, please re-order -----' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ('UPDATE COMPLETE');
    END IF;
END;
/

Now to test the trigger i am going to update the quantity in hand for a particular product;

UPDATE STOCK_INVENTORY
SET S_QUANTITY = 4
WHERE BR_ID = 1
AND P_ID = 6;

And the result is :

Warning: ----- product with ID (1) has (4) units remaining, please re-order -----

which means that the trigger works. However the stock_inventory has a few products that stock level is less than 10, but with the conditional I have, it only shows the message for the current transaction.

here is a list of a few products in stock_inventory, this is a linker table;

SQL> SELECT * FROM STOCK_INVENTORY;

     BR_ID|      P_ID|S_QUANTITY
----------|----------|----------
         1|         1|        10
         1|         6|         4
         1|         3|        30
         1|         8|        24
         1|         9|        18
         2|        10|         9
         2|         2|        10
         2|        20|        15
         2|        16|        17
         2|        13|        20
         3|        21|        15

How can I show a list of the products where quantity is less than 10? I am pretty new to oracle. Thanks alot.

Thanks

Improve this question

1 Answer 1

Reset to default
2

First off, why do you want to use a trigger for this sort of thing? And why do you want the trigger to list products that need to be reordered other than those that the current transaction is affecting? Since there is absolutely no guarantee that anyone will ever see anything written to DBMS_OUTPUT, since it seems counterintuitive to alert someone placing an order for product 1 to reorder product 6, and since you would generally want to separate the updating of inventory from the ordering of inventory, the entire approach doesn't seem to make sense.

Assuming this is a homework assignment, however you could use a statement-level trigger instead

CREATE OR REPLACE TRIGGER trg_new_reorder_stock
  AFTER INSERT OR UPDATE ON stock_inventory
BEGIN
  FOR x IN (SELECT *
              FROM stock_inventory
             WHERE s_quantity <= 10)
  LOOP
    dbms_output.put_line( 'Reorder ' || x.p_id || ' it has only ' || x.s_quantity || ' units remaining.' );
  END LOOP;
END;

Since this involves a statement-level trigger, you can query the stock_inventory table without worrying about getting a mutating table exception. However, this also means that you are executing a potentially expensive query against the entire table every time you insert a single row which is inefficient.

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

5 Comments

Thanks Justin and nice to see you around you've helped me greatly last year, thanks for pointing those issues out, indeed i dont really need to know the list of products less than 10, it can be done with a simple sql query. and yes i need to know only the quantity on hand for the current transaction.
As for the trigger, I am so new that i still dont know when is it better to use a triger a function or procedure and cursor. the question i need to answer in my assignment is: 3.3. Identify when the stock reaches its minimum on-hand quantity, the item in low supply must be re-ordered by a staff member, and must be placed with one of the shops preferred suppliers.
How would i output the dbms output to a txt? i have been reading on spools and procedures but is rather confusing
@JoseDavidGarciaLlanos - Without context, it is hard to know what the assignment is asking. Surely that would be a more appropriate question to ask of your professor (or TA). This is particularly true because professors often want students to do things in homework that they would never do in the real world because the goal of homework isn't always to model the right way to handle something in reality. Personally, I would tend to create a view of the items that need to be reordered.
@JoseDavidGarciaLlanos - If you want to write data to a file from PL/SQL, you would use the UTL_FILE package. That will create a file on the database server. If you are using SQLPlus, you can create a SQLPlus script that uses the SPOOL command to write to a file on the client. But that SQLPlus script can then only be used from SQLPlus (or a handful of client utilities that understand some subset of the SQL*Plus commands) whereas a PL/SQL block can be executed by a much wider range of applications.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.