Question
Use the PVFC database and APEX for this problem. First create a new table named Bill_T using the code given below -- CREATE TABLE Bill_T(
Use the PVFC database and APEX for this problem. First create a new table named Bill_T using the code given below --
CREATE TABLE Bill_T( CustomerID number(11,0),
ProductID number(11,0),
Quantity number(11,0),
OrderDate date,
Charge number(11,2));
We need to write an INSERT trigger using PL/SQL (named t_customer_bill) to do the following:
1) When you insert a new bill into the BILL table, you need only to provide values for CustomerID, ProductID, and Quantity;
2) Get the state info of the customer (from the CUSTOMER table) based on the CustomerID. Then you need to determine the tax rate based on the state info. Just for practice, let us assume: the tax rate in FL is 6%, and 10% in all other states;
3) Get the product standard price (from the PRODUCT table) based on the ProductID;
4) Assign the current system date (i.e., using SYSDATE) to OrderDate;
5) Use the formula [Charge = Quantity * Standard_Price * (1 + Tax_Rate)] to get the charge and assign it to Charge.
After you implement the trigger, run the following three queries to test if it works correctly.
INSERT INTO BILL_T(CustomerID, ProductID, Quantity) VALUES(1, 2, 5);
INSERT INTO BILL_T(CustomerID, ProductID, Quantity) VALUES(2, 7, 1);
SELECT * FROM Bill_T;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started