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
Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

IF Statement The IF statement executes a series of statement conditionally. Whether the series is executed or not depends on the value of the Boolean expression. Syntax:

Parameter and Keyword Description: dynamic_string: This is a string variable, literal, or expression which represents a SQL statement or the PL/SQL block. define_vari

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Methods: In normal, a method is a subprogram declared in an object type specification using the keyword MEMBER or STATIC. The method cannot have similar name as the object typ

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Assignment of Variable - Updating a Variable Syntax: SET SN = SID ('S2'); This can obviously be read as "set the variable SN to be equal in value to SID ( 'S2' )".

MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief