How to get date from two tables using trigger before insert into third table?

Question

I'm trying to create a trigger TrgDisAmount to read PROD_NAME and PRICE from PRODUCT table and to calculate the DISCOUNT and AMOUNT of SALES Table for every new row inserted with the values of SERIAL and PCODE.

These are the tables information

Table SALES (SERIAL, PCODE, PROD_NAME, PRICE, DISCOUNT, AMOUNT)

Table PRODUCT (PCODE, PROD_NAME, PROD_CAT, PRICE)

Table DISCOUNT (PROD_CAT, DISCOUNT_RATE)

Note:

1. Enter the values for SERIAL and PCODE only, the remaining columns should entered by the trigger TrgDisAmount only
2. Get the DISCOUNT_RATE using Function GetDiscount.

I have already created GetDiscount function to get DISCOUNT_RATE from table DISCOUNT.

This is my try:

create or replace trigger TrgDisAmount
  before insert on SALES for each row
begin
  if :new.PCODE = :old.PRODUCT.PCODE then

    :new.PROD_NAME := :old.PRODUCT.PROD_NAME;
    :new.PRICE := :old.PRODUCT.PRICE;
    :new.DISCOUNT := :old.product.PRICE / (GetDiscount(:old.PRODUCT.PROD_CAT));
    :new.AMOUNT := :new.PRICE - :new.DISCOUNT;
  end if;

  insert into SALES columns (PROD_NAME, PRICE, DISCOUNT, AMOUNT) 
  values (:new.PROD_NAME, :new.PRICE, :new.DISCOUNT, :new.AMOUNT);
end;
/

When I run that block it shows me this error:

PLS-00049: bad bind variable 'OLD.PRODUCT'

I use Table_Name.Column_name to reach to the specific column. Is it legal to do it?

---

Sample output should be like this:

SQL> insert into sales values (1,'MB-101',null, null, null, null);

Result in SALES table:
   SERIAL : 1,
   PCODE : MB-101,
   PROD_NAME : IPHONE 6+,
   PRICE : 250 ,
   DISCOUNT : 25,
   AMOUNT : 225

Answer 1

When you create a before insert trigger, you only have to set the :new values. You don't actually insert into the table. That is the next step in the processing.

Also, in an insert trigger, only refer to the :new values, not the :old. There is no :old value, which is the specific error you are getting.

Then, you need to do a query to get the relevant information from the other tables. The result should look something like this:

create or replace trigger TrgDisAmount
before insert on SALES
for each row
begin
    select p.price / d.discount into :new.discount
    from product p join
         discount d
         on p.prod_cat = d.prod_cat
    where :new.pcode = p.pcode;
    :new.AMOUNT := :new.PRICE - :new.DISCOUNT;
end;

The arithmetic for defining the discount looks very suspicious. A 10% discount would be represented as 1.11 using this methodology. However, this is the arithmetic in your question and you provide no sample data to suggest any other method.

Answer 2

Can you try changing , the way you are calling your function.

As mentioned by Gordon, :old shouldn't be used in this case

CREATE or replace trigger TrgDisAmount 
Before insert 
on SALES 
for each row 

DECLARE
v_prod_cat varchar2(20);

BEGIN 

select PROD_NAME into :NEW.PROD_NAME
from PRODUCT 
where PRODUCT.PCODE = :NEW.PCODE;  

select  PRICE into :NEW.PRICE 
from PRODUCT 
where PRODUCT.PCODE = :NEW.PCODE; 

select  PROD_CAT into v_prod_cat from discount where  PROD_CAT 
in( select PROD_CAT from PRODUCT where  PCODE = :NEW.PCODE); 

select  GetDiscount(v_prod_cat) into :NEW.discount from dual;

 :new.AMOUNT := :new.PRICE - :new.DISCOUNT;

END;
/

Trigger created.   
SQL> show err;                                                        
 No errors.

insert into sales  values(701,1,NULL,NULL,NULL,NULL);