[Solved]Pl Sql Brewbeans Bbproductrequest Table Requests Refill Stock Levels Inserted Automaticall Q37034947

In PL/SQL:

Brewbeans has a BB_PRODUCT_REQUEST table where requests torefill stock levels are inserted automatically via a trigger. Afterthe stock level falls below the reorder level, this trigger firesand enters a request in the table. This procedure works great;however, when store clerks record that the product request has beenfilled by updating the table’s DTRECD and COST columns, they wantthe stock level in the product table to be updated. Create atrigger named BB_REQFILL_TRG to handle this task, using thefollowing steps as a guideline:

1. In SQL Developer, run the following INSERT statement tocreate a product request you can use in this exercise:

INSERT INTO bb_product_request(idRequest, idProduct,dtequest, qty)
VALUES (3, 5, sysdate, 45);
COMMIT;

2. Create the trigger (BB_REQFILL_TRG) so that it fires when areceived date is entered in the BB_PRODUCT_REQUEST table. Thistrigger needs to modify the STOCK column in the BB_PRODUCT table toreflect the increased inventory.

3. Now test the trigger. First, query the stock and reorder datafor product 5.

4. Now update the product request to record it as fulfilled byusing the UPDATE statement
UPDATE BB_PRODUCT_REQUEST
SET DTRECD = SYSDATE, COST = 225
WHERE IDREQUEST = 3;

5. Issue queries to verify that the trigger fired and the stocklevel of the product 5 has been modified correctly. Then issue aROLLBACK statement to undo the modifications.

rksheet Query Builder 1 SELECT stock, reorder FRO b poduct SELECT . FRbbpoduct tequest ォ0日ニ Task completed n 0.01S seconds STOCK REORDER Stock level currently below reorder level 45 41 IDREQUEST IDPRODUCT DTREQUEST DTRECD COST OTY IDVENDER Currentrequest for a quantity of 45 — 5 09-APR-12 45 + Doms Output x Log Buffer Size: 20000 An UPDATE statement that records receiving a request fires the trigger Worksheet y Builder UPDATE bb product xequest SET dtAecd·SYSDATE, cost . 225 SELECT FROM bb product request SELECT stock, reorder FROM bb product Script OutputQuery Resultx I Task completed in 0.046 seconds tows updated. IDREQUEST IDPRODUCT DTREUEST DTRECD COST OTY IDYENDER 5 09-APR-12 09-APR-1 225 45 Order received STOCK REORDER 45 Stock level increased Buffer Size: 20000 Show transcribed image text rksheet Query Builder 1 SELECT stock, reorder FRO b poduct SELECT . FRbbpoduct tequest ォ0日ニ Task completed n 0.01S seconds STOCK REORDER Stock level currently below reorder level 45 41 IDREQUEST IDPRODUCT DTREQUEST DTRECD COST OTY IDVENDER Currentrequest for a quantity of 45 — 5 09-APR-12 45 + Doms Output x Log Buffer Size: 20000
An UPDATE statement that records receiving a request fires the trigger Worksheet y Builder UPDATE bb product xequest SET dtAecd·SYSDATE, cost . 225 SELECT FROM bb product request SELECT stock, reorder FROM bb product Script OutputQuery Resultx I Task completed in 0.046 seconds tows updated. IDREQUEST IDPRODUCT DTREUEST DTRECD COST OTY IDYENDER 5 09-APR-12 09-APR-1 225 45 Order received STOCK REORDER 45 Stock level increased Buffer Size: 20000

Expert Answer

---

Answer to In PL/SQL: Brewbeans has a BB_PRODUCT_REQUEST table where requests to refill stock levels are inserted automatically via… . . .