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

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Use external routines - improve performance of application, Use External Ro...

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Inserting objects in pl sql, Inserting Objects: You can use the INSERT...

Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

Like operator-comparison operators, LIKE Operator You use the LIKE opera...

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Package specification in pl/sql , Package Specification The package sp...

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Using host arrays - bulk bind performance improvement, Using Host Arrays ...

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Custom ms access database designed, I would like to have a custom MS Access...

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Triggers, At times, customers make mistakes in submitting their orders and ...

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 pr

Write a pl/sql anonymous block that accepts an employee id, Based on the EM...

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

Defining ref cursor types, Defining REF CURSOR Types To make cursor va...

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

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