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

Use `:NEW.ID_Payment` instead of `:NEW.payment.ID_payment`. Best of luck. — Bob Jarvis - Слава Україні, Dec 24 '17 at 04:20
Is it your intention to update the entire table each time someone modifies a single row's get_out? If you only want to update the cost of the row being modified, just set :new.cost to a cable. Avoid running UPDATE statements in triggers, here be dragons — Caius Jard, Dec 24 '17 at 04:50
Was `to_date(payment.get_out)-to_date(payment.get_in)` supposed to be `trunc(payment.get_out)-trunc(payment.get_in)` i.e. the elapsed time in days? If `get_in` and `get_out` are already dates then using `to_date` on them can't be right. — William Robertson, Dec 24 '17 at 12:19
@Bob thank bob, it's working, but when i insert new value to get_out it say " table payment is mutating, trigger/function may not see it " — Hilfit19, Dec 25 '17 at 11:36
@WilliamRobertson The code "to_date(payment.get_out)-to_date(payment.get_in)" is correct, i have tried it, the problem is to get value of price from table place in trigger code — Hilfit19, Dec 25 '17 at 11:42
I realise `to_date(a_date_column)` happens to work and isn’t the problem you are trying to solve. It’s still always a bug in my opinion. Would you `to_char(a_varchar2_column)` or `to_number(a_number_column)`? — William Robertson, Dec 25 '17 at 11:48
@Hilfit19 - there are a lot of questions on StackOverflow which deal with the mutating table issue - for example, [this one](https://stackoverflow.com/questions/29489951/oracle-trigger-after-insert-or-delete). I suggest you look them up and read through them. Best of luck. — Bob Jarvis - Слава Україні, Dec 25 '17 at 14:39

Barbaros Özhan · Answer 1 · 2017-12-24T05:32:35.703

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.

thank @Barbaros, I have tried using your code, but still say trigger created with compilation errors. Yes, the type of get_out and get_in are date, but I have set nls_date_format so the time is available — Hilfit19, Dec 25 '17 at 11:46

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

1 Answers1