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
Use Serially Reusable Packages To help you to manage the use of memory, the PL/SQL gives the pragma SERIALLY_ REUSABLE that mark some packages as serially reusable . So mark

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Equivalences & Rewrite Rules: If notice that as well as allowing us to prove trivial theorems, and tautologies enable us to establish that certain sentences are saying the sam

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh


Important Distinctions The list of important distinctions are given below: Value versus variable Syntax versus semantics Variable versus variable reference

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an