User Account

All Pages

S, PL-SQL Programming

Assignment Help:
Question 1. Update stock levels when the order is cancelled

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;

Related Discussions:- S

Select into statement - syntax, SELECT INTO Statement   The SELECT INT...

SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Assignment2, How do I display usernames for students from a student table, ...

How do I display usernames for students from a student table, assigning each student a username initials001 (initials is the actual student initials), and if the students initials

Pits, PITS Depressions in secondary cell wall is called pit. A pi...

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Calculate the total shopper spending, Many of the reports generated from th...

Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Parameter and keyword description - %type attribute, Parameter and Keyword ...

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

Theory of eternity of life - origin of life, THEO R Y OF ETERNITY OF LIFE...

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Union all - sql, UNION ALL - SQL Further varieties of UNION arise when...

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Delete statement - syntax, DELETE Statement The DELETE statement elimin...

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

Example of wrap operator - sql, Example of WRAP Operator - SQL The eff...

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Cursor variables, What Are Cursor Variables  ? The Cursor variables ar...

What Are Cursor Variables  ? The Cursor variables are like C or Pascal pointers that hold the memory location (address) of some item rather of the item itself. Therefore, decl

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd