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

Ask an Expert

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

Write your message here..

Related Questions

Goto statement - syntax, GOTO Statement   The GOTO statement branches ... GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Exception handling, set serveroutput on declare a number(5); b n... 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_d

Natural join - sql, Natural Join - SQL In the absence of NATURAL JOIN... Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Overloading method in pl/sql, Overloading: Similar to packaged subprog... Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Literals in pl/sql, Literals A literal is an explicit numeric, string... Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

Calculating a Shopper''s Total Spending, Many of the reports generated from... Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

Features of pl/sql, Main features of PL/SQL A good way to get familiar ... Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

How exceptions propagate in pl/sql programming?, How Exceptions Propagate ?... How Exceptions Propagate ? Whenever an exception is raised, and if the PL/SQL cannot find a handler for it in the present subprogram or block, the exception propagates. That is

Authorize and fetch data from instagram, Authorize and fetch data from Inst... Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Constants and variables in pl sql, Constants and Variables:   You can... Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor