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

Pragma restrict_references in pl sql, Using Pragma RESTRICT_REFERENCES: ...

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Closing a cursor - explicit cursor, Closing a Cursor The CLOSE stateme...

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

I need sql to infopath data connection, I need SQL to infopath data connect...

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Sql, Write a cursor to open an employee database and fetch the employee rec...

Write a cursor to open an employee database and fetch the employee record whose age is greater than 45

Using %type-declarations in sql, Using %TYPE The %TYPE attribute gives th...

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Long and long raw in pl/sql, LONG and LONG RAW You use the LONG datatyp...

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

%type attribute - syntax, %TYPE Attribute The %TYPE attribute gives the...

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Data types in sql - interval, Data Types in SQL - Interval, Boolean I...

Data Types in SQL - Interval, Boolean INTERVAL for values denoting, not intervals (!) but durations in time, such as 5 years, 3 days, 2 minutes, and so on. BOOLEAN, con

produce vertical output format- oracle, Create a Oracle procedure to produ...

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

How transactions guard your database, How Transactions Guard Your Database ...

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

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