Update stock levels, PL-SQL Programming

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and that updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order was not completed. Create a trigger named BB_ORDERCANCEL_TRG to accomplish this task by considering the following items:

1.  The trigger needs to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates order cancellation.

2.  Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop may be an appropriate mechanism to update the stock level of each item.

3.  Keep in mind that coffee can be ordered in half o whole pounds.

4.  Use basket 6 for testing. Note that this basket contains two items.

a.  Open or return to SQL Plus. Execute the following statement to test the trigger:

Insert into bb_basketstatus (idstatus, idbasket, idstage, tdstage) values (bb_status_seq.nextval, 6, 4, sysdate);

b.  Execute queries to confirm that the basket order status and product stock levels have been appropriately modified by the trigger.

c.  Be sure to run the following statement to disable this trigger so that it does not affect other products:

Alter trigger bb_ordcancel_trg disable;

Posted Date: 3/12/2013 2:06:56 AM | Location : United States







Related Discussions:- Update stock levels, Assignment Help, Ask Question on Update stock levels, Get Answer, Expert's Help, Update stock levels Discussions

Write discussion on Update stock levels
Your posts are moderated
Related Questions
Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra