Oracle SQL trigger before update to set column value from another entity

Question

I want to create an update trigger for a table called payment that will update the cost column with the difference between get_out and get_in multiplied price value from another entity

I don't have much experience with creating triggers but so far this is what I've got

CREATE OR REPLACE TRIGGER o1 BEFORE UPDATE OF get_out ON payment 
FOR EACH ROW

BEGIN                            
    UPDATE payment
    SET payment.cost = ROUND (24 * (to_date(payment.get_out)-to_date(payment.get_in)),0) *
        (SELECT price FROM payment,book,place              
           WHERE book.ID_place = place.ID_place
                 AND payment.ID_payment = book.ID_payment
                 AND payment.ID_payment = :NEW.payment.ID_payment
        )
        ;
END;
/

I get this error when creating the trigger:

Trigger created with compilation errors.

LINE/COL   ERROR
---------- -----------------------------------------------------------
7/43       PLS-00049: bad bind variable 'NEW.PAYMENT'

Any suggestion? Thanks

Answer 1

you're trying to update your table payment in your update trigger. Just assign the returning value of join to the column :new.cost.

prefer using the JOIN ON syntax instead of deprecated former syntax, and take the table book as the first in tables order syntax.

there's a careless attempt on :new.payment.ID_payment, which should be :new.ID_payment instead.

CREATE OR REPLACE TRIGGER o1 BEFORE UPDATE OF get_out ON payment 
FOR EACH ROW

BEGIN                            
    :new.cost := round(24 * (to_date(:new.get_out)-to_date(:new.get_in)),0) *
        ( select price 
            from book b 
            join payment py on py.id_payment = b.id_payment
            join place pl   on pl.id_place   = b.id_place            
           where py.id_payment = :new.id_payment            );
END;
/

By the way, if the columns get_out and get_in are of type date, and just hold date value without date and time info ( 24.12.2017, instead of 24.12.2017 08:00 ), then no need to cast using to_date in your substraction.