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
DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

write the program for traffic control system with 10 second, 15 secod, and 20 second delay

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot