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
Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

Seeking a programmer to design a legal document with pre-existing fields that could allow the auto-population of client(s) information (i.e. Name, Account Number, Address etc.) int

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

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

Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a