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

Exceptions are raised - Using Raise statement, How Exceptions Are Raised ...

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

Delete command - sql, DELETE Command - SQL Loosely speaking, DELETE re...

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

Table comparison - sql, Table Comparison - SQL The following definitio...

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Methods in pl/sql, Methods: In normal, a method is a subprogram declar...

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

Delimiters, Delimiters A delimiter is a simple or compound symbol whi...

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

Closest approximation to relational union - sql, Closest Approximation to R...

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Use native dynamic sql - improve performance of application, Use Native Dyn...

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

EXCEPTION handling, set serveroutput on declare a number(5); b numb...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Implement a new discount for return shoppers, Brewbean's is implementing a ...

Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

Using savepoints, Using Savepoints The scope of the savepoint is a tra...

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

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