oracle - How to execute trigger and procedure in oracle

Question

I am working on my Project 'Supermarket Billing Management System' since I am a beginner I m facing a lot of issues while making project. Here I've already created a trigger and a procedure but I don't know how I can execute it, I've created a trigger for a total price of a single Product i.e ProdTotal = ProdPrice * ProdQuantity;. That means whenever a user enters some data in the Products table, then this trigger must get executed, but I don't know how to execute it, similarly, I've created a procedure to calculate the total price of all the products purchased by a single customer. Just like when you go to the supermarket or any store then after purchasing the items, you get a bill and there is the final total amount. I m not even exactly sure whether my procedure code is right or wrong, though it was created successfully, I m not sure whether it will give me the exact output which I want, So if you can help me then, please let me know, I've researched a lot from different websites and also from many youtube videos but seriously I am not getting how to solve it, so please help me!

Code:

Products table

create table Products
    ( ProdId number primary key,
      ProdNum number not null unique,
      ProdName varchar2(15),
      ProdPrice int,
      ProdQuantity int,
      ProdCustId int references Customers,
      ProdOrdId int references Orders,
      ProdStoreId int references Stores
   );

Payments table

create table Payments
    ( PayId int primary key,
      PayDate date,
      ProdTotal int,
      FinalTotal int,
      PayOrdId int references orders,
      PayProdId int references Products,
      PayCustId int references Customers
    );

Trigger code

create trigger PROD_TOTAL
    AFTER INSERT ON Products
    BEGIN
    UPDATE Payments
    SET ProdTotal = (SELECT Products.ProdPrice * Products.ProdQuantity FROM Products);
    END;
    /

Procedure code

create procedure FINAL_TOTAL(C IN NUMBER, T OUT NUMBER)
    IS
    BEGIN
    UPDATE Payments
    SET FinalTotal = FinalTotal + ProdTotal
    WHERE PayCustId = C;
    Commit;
    SELECT FinalTotal into T FROM Payments WHERE PayCustId = C;
    END;
    /

Insert statement in Product table:

insert into Products values(1,1001,'Syrup',30,4,1,1,1);

Insert statements in Payments table:

insert into Payments(PayId, PayDate, PayOrdID, PayProdId, PayCustId)
values(1,date'2020-10-07',1,1,1);

Output:

select * from products;

    PRODID    PRODNUM PRODNAME         PRODPRICE PRODQUANTITY PRODCUSTID
---------- ---------- --------------- ---------- ------------ ----------
 PRODORDID PRODSTOREID
---------- -----------
         1       1001 Syrup                   30            4          1
         1           1

select * from Payments;

     PAYID PAYDATE    PRODTOTAL FINALTOTAL   PAYORDID  PAYPRODID  PAYCUSTID
---------- --------- ---------- ---------- ---------- ---------- ----------
         1 07-OCT-20                                1          1          1

                                               

Now here, as you can see PRODTOTAL and FINALTOTAL column is blank, I know why it is blank because I didn't enter any value. And the reason why I didn't enter any value in these two columns is that I want, the system should automatically calculate that calculation with the help of trigger and procedure and, I can't even remove trigger and procedure because it's mandatory in our project to use both of these concepts. So please help me!!!

标签： oraclestored-procedurestriggers

Answer 1

As already proposed, first try to get the design right with respect to your requirements. You can implement many constraints just by designing correctly your database schema.

Stay away from triggers and PL/SQL for as long as possible. It will force you to better design in the end and will pay off.

Before using triggers for business logic, try to use views for things that can be selected. That's what the database is for.

When you are "done", test for the performance and if it's suboptimal, improve your schema. If nothing helps, start using triggers for business logic.

I've put together a sample with views I am talking about. I hope it can get you started.

create table Products (
  ProdId number generated always as identity primary key
, ProdName varchar2(20) not null
);

create table Stores (
  StoreId number generated always as identity primary key
, StoreName varchar2(20) not null
);

create table Customers (
  CustomerId number generated always as identity primary key
, CustomerName varchar2(20) not null
);

create table Prices (
  PriceId number generated always as identity primary key
, ProdId number not null
, Price number
, ValidFrom date default on null sysdate
, constraint fk_Prices_Product foreign key (ProdId) references Products (ProdId)
);

create unique index uniq_prices_product_price on Prices (ProdId, ValidFrom);

create table Orders (
  OrderId number generated always as identity primary key
, CustomerId number not null
, StoreId number not null
, OrderedAt date default on null sysdate
, constraint fk_Orders_Customer foreign key (CustomerId) references Customers (CustomerId)
, constraint fk_Orders_Store foreign key (StoreId) references Stores (StoreId)
);

create table OrderLines (
  OrderLineId number generated always as identity primary key
, OrderId number not null
, ProdId number not null
, ProdQuantity number not null
, constraint fk_OrderLines_Order foreign key (OrderId) references Orders (OrderId)
, constraint fk_OrderLines_Prod foreign key (ProdId) references Products (ProdId)
);

create table Payments (
  PaymentId number generated always as identity primary key
, OrderId number not null
, PaidAt date default on null sysdate
, PaidAmount number not null
, constraint fk_Payments_Order foreign key (OrderId) references Orders (OrderId)
);

create view Prices_V as
select
  p.*
, coalesce(
    lead(p.ValidFrom) over (partition by p.ProdId order by p.ValidFrom)
  , to_date('9999', 'YYYY')
  ) ValidTo
from Prices p;

create view Orders_V as
select
  o.*
, (
    select sum(ol.ProdQuantity * p.Price)
    from OrderLines ol
    join Prices_V p on (p.ProdId = ol.ProdId and o.OrderedAt between p.ValidFrom and p.ValidTo)
    where o.OrderId = ol.OrderId
  ) Total
, (
    select sum(PaidAmount)
    from Payments p
    where p.OrderId = o.OrderId
  ) TotalPaid
from Orders o;

insert into Products(ProdName)
select 'Prod A' from dual union all
select 'Prod B' from dual;

insert into Stores(StoreName) values ('Store A');

insert into Customers(CustomerName) 
select 'Customer A' from dual union all
select 'Customer B' from dual;

insert into Prices(ProdId, Price, ValidFrom)
select 1, 10, sysdate - 10 from dual union all
select 1, 12, sysdate - 2 from dual union all
select 1, 14, sysdate + 3 from dual union all
select 2, 100, sysdate - 10 from dual union all
select 2,  90, sysdate - 2 from dual union all
select 2,  null, sysdate + 5 from dual;

insert into Orders(CustomerId, StoreId, OrderedAt)
select 1 cid, 1 stoid, sysdate - 5 from dual union all
select 2, 1, sysdate - 5 from dual union all
select 2, 1, sysdate - 1 from dual;

insert into OrderLines(OrderId, ProdId, ProdQuantity)
select 1 ordid, 1 prodid, 3 prodquant from dual union all
select 1, 2, 2 from dual union all
select 2, 2, 10 from dual union all
select 3, 2, 10 from dual;

insert into Payments(OrderId, PaidAmount) values (2, 500);


select * from Prices_V order by ProdId, ValidFrom;
select * from OrderLines order by OrderId, ProdId;
select * from Orders_v order by OrderId;

Some of the ideas in there:

1. Prices are stored in separate table, reference the product and have validity so that product price can change over time. Price view have ValidTo column added so it's easier to work with
2. There is a unique index on Prices so that we cannot have 2 prices for the same product at the same time
3. You can have many items in order, so that's why there is Orders and OrderLines tables in 1-to-many relationship
4. In Order_V the total paid is shown (using a subquery on Payments) and the total order values is shown (using a subquery on OrderLines and Prices, date of order is used to get prices form the correct period)

Based on the schema you will se what things you can represent and which you cannot. It's your job to make it match your requirements :)

And now I've come to the point you say triggers and procedures are mandatory in your project. Hence I have a proposal:

1. Create a procedure that will allow users to create new price for a product. It should definitely check that the validity does not start in the past. Then implement another one that allows for changing the valid to date (also cannot end in the past). You can than revoke any insert/update privileges on Products table and force users to use your procedures that will contain this business logic.
2. Create a table PricesLog and trigger on Prices that will insert the PriceId, old.Price, new.Price, sysdate and User to the log on any inserts/updates to the prices table.