CREATE TABLE Bill_T( CustomerID number(11,0), ProductID number(11,0), Quantity n

Question

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;

Explanation / Answer

CREATE OR REPLACE TRIGGER t_customer_bill

AFTER INSERT

ON Bill_T

FOR EACH ROW

Declare

varState varchar2(30);

Tax_Rate number(11,2);

varCharge number(11,2);

varQty number(11,0);

Standard_Price number(11,2);

varCustomerID number(11,0);

varProductId number(11,0);

Begin

select :new.CustomerID into varCustomerID from Bill_T;

select state into varState from Customer where customerId = varCustomerID;

select ProductId into varProductId from Bill_T where customerId = varCustomerID;

if (varState = 'FL') then

Tax_Rate := 6/100;

else

Tax_Rate := 10/100 ;

endif

select Product_price into Standard_Price into varState from PRODUCT where ProductID = varProductId;

select Quantity into varQty from Bill_T where customerId = varCustomerID

varCharge := varQty * Standard_Price * (1 + Tax_Rate);

update BILL_T set order_date = SYSDATE,Charge = varCharge where customerId = varCustomerID and ProductID = varProductId;

End

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.