Create a product request you can use in this assignment

Assignment Help PL-SQL Programming

Reference no: EM133043848

Assignment 9-2: Updating Stock Information When a Product Request Is Filled

Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock levels are inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns, they want the stock level in the product table to be updated. Create a trigger named BS_REQFILL_TRG to handle this task, using the following steps as a guideline:

1. In SQL Developer, run the following INSERT statement to create a product request you can use in this assignment:
INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty) VALUES (3, 5, SYSDATE, 45);
COMMIT;

2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.

3. Now test the trigger. First, query the stock and reorder data for product 5, as shown in Figure 9-38.

885_Stock Information.jpg

FIGURE 9-38 Querying the data for product 5 stock and reorder amount

4. Now update the product request to record it as fulfilled by using the UPDATE statement shown in Figure 9-39.

1315_Stock Information1.jpg

FIGURE 9-39 Updating the product request Chapter 9

5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue a ROLLBACK statement to undo the modifications.

6. If you aren't doing Assignment 9-3, disable the trigger so that it doesn't affect other assignments.

Reference no: EM133043848

Questions Cloud

Progress on the inventory-handling processes : Progress on the inventory-handling processes; however. they hit a snag when a store clerk incorrectly recorded a product request as fulfilled

Organizational culture with the wrong tolerance : For this discussion, the new CEO comes to you and tells you that he is a positive person, but there are things he wants you to know that he is very

Identify related parties and related party transactions : Explain the inherent limitations which mean that auditors may not identify related parties and related party transactions

Consumer assessment of healthcare providers and systems : What are Hospital Consumer Assessment of Healthcare Providers and Systems (HCAHPS), and how do they relate to the need for quality?

Create a product request you can use in this assignment : Updating Stock Information When a Product Request Is Filled - Querying the data for product 5 stock and reorder amount

What are seductive details : What are seductive details? Should they be included in the delivery of a training program? If yes, explain how to do this and give an example.

What the total assets that will appear in the statement : The total assets of the Sufian Company (subsidiary) appeared in the book at 220,000 dinars. What the total assets that will appear in the statement

Pain points of such concert-going experiences : What are the pain points of such concert-going experiences? Who are the customers of traditional classical concerts? Andre Rieu Case

Advice mrs naidu if she her plans to pay down the home loan : The investment property is earning a profit before tax of $12,000. Advice Mrs Naidu if she her plans to pay down the home loan is appropriate

User Account

All Pages