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
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

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

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

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

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Using LIMIT For nested tables, that have no maximum size, the LIMIT returns NULL. For varrays, the LIMIT returns the maximum number of elements that a varray can have (that yo

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai