Implement a new discount for return shoppers, PL-SQL Programming

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 variable named PV_DISC_NUM during the ordering process. This count needs to be tested upon checkout to determine if a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is set from 0 to 1), the PV_DISC_NUM packaged variable is checked. If it is equal to 5, then set a second packaged variable named PV_DISC_TXT to 'Y'. This variable is used in calculating the order summary so that a discount is applied if appropriate.

Create a package specification named DISC_PKG that contains the needed packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger by using the following update statement:

Update bb_basket set orderplaced =1 where idbasket=13;

Reset the ORDERPLACED to zero for the basket used (this is done as needed to complete trigger testing). Also, disable this trigger when completed so that it does not affect other assignment questions.

Posted Date: 3/12/2013 2:08:02 AM | Location : United States







Related Discussions:- Implement a new discount for return shoppers, Assignment Help, Ask Question on Implement a new discount for return shoppers, Get Answer, Expert's Help, Implement a new discount for return shoppers Discussions

Write discussion on Implement a new discount for return shoppers
Your posts are moderated
Related Questions
Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S

SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

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