1

I have the following schema:

CREATE TABLE ORDER(
    OrderId   NUMBER PRIMARY KEY,
    Value     NUMBER);

CREATE TABLE ORDERITEM(
    OrderItemId     NUMBER PRIMARY KEY,
    OrderId         NUMBER,
    ISBN            NUMBER,
    Quantity        NUMBER,
    Price           NUMBER,
    CONSTRAINT FK_OrderID 
    FOREIGN KEY (OrderId)
    REFERENCES ORDER (OrderId));

In the value field I store the order total price [sum(quantity*price)]

I need to create a trigger for add 5% discount from the total price and store it in the value field in order table, if the customer buy more than 6 books.

I need the trigger for Insert/Update/Delete.

Edit: Here is my current trigger for filling the value field:

create or replace trigger val
before insert or update or delete on orderitem
for each row
begin
    if inserting then    
        Update order 
        set value = ((
                    select sum(price * quantity) 
                    from orderitem 
                    where orderid = :NEW.orderid 
                    group by :New.orderid)
                    + (:New.price * :New.quantity))
        where orderid = :New.orderid;
    elsif updating then
        Update order set value = (value + (:New.price * :New.quantity))
        where orderid = :New.orderid;
        Update order set value = (value - (:old.price * :old.quantity))
        where orderid = :old.orderid;
    elsif deleting then
        Update order set value = (value - (:old.price * :old.quantity))
        where orderid = :old.orderid;
    end if;        
end;
  • You can update using a single statement: set value = value + (new) - (old). OrderId is PK, it doesn't change. – McNets Dec 23 '18 at 23:46
  • OrderID is FK, in OrderItem Table, i need to add the new value into the new order, and remove it from the old order – J. DOE Dec 23 '18 at 23:54
  • @J.DOE - Why not use a VIEW or MATERIALIZED VIEW? – Michael Kutz Dec 24 '18 at 15:14
  • I need to add 5% discount on the value ; if the buyer buy more than 4 books; so i need a trigger to automatically calculate the price and add discount when the order has new orderitem or update ir delete; how can Materialized view be useful?? – J. DOE Dec 25 '18 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.