CREATE TABLE Bill_T( CustomerID number(11,0), ProductID number(11,0), Quantity n
ID: 3709417 • Letter: C
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 TRIGGER trgAfterInsert ON [dbo].[Bill_T]
FOR INSERT
AS
declare @CustomerID int;
declare @ProductID number ;
declare @Quantity number;
declare @OrderDate date ;
declare @Charge number ;
declare @State varchar(30);
declare @Standard_Price int;
set @Standard_Price=100;
select @CustomerID =i.CustomerID from inserted i;
select @ProductID =i.ProductID from inserted i;
select @Quantity =i.Quantity from inserted i;
if(@State =="FL")
set @Charge=@Quantity*@Standard_Price*(1+6/100)
else
set @Charge=@Quantity*@Standard_Price*(1+10/100)
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(CustomerID,ProductID,Quantity,OrderDate,Charge,Audit_Action)
values(@CustomerID,@ProductID,@Quantity,getdate(),@Charge,@Audit_Action);
PRINT 'AFTER INSERT trigger fired.'
GO
OrderDate date,
Charge number(11,2));