Insert inside the trigger in oracle

Question

Im trying to make simple trigger for students project. It will be firing on the prices table.

CREATE TABLE "STD_USER"."HT_PRICES" 
(   "PRC_ID" NUMBER, 
"PRC_DATE_FROM" DATE, 
"PRC_DATE_TO" DATE, 
"PRC_STD_ID" NUMBER, 
"PRC_AMOUNT" NUMBER
)

The main goal of the trigger is to checking if any reservation do not have any reference to updated Prices row.

If any row actually have reference on updated row we will need to insert new price into the prices table with starting date (PRC_DATE_FROM) equal to the end of the last reservation. Trigger body:

create or replace 
TRIGGER "TRG_NEW_PRICE" BEFORE UPDATE ON HT_PRICES 
REFERENCING OLD AS old NEW AS new 
FOR EACH ROW
DECLARE
  v_last_rsv_date DATE;
  v_temp_date_to DATE;
  v_std_id NUMBER;
  v_amount NUMBER;
BEGIN
  BEGIN
    SELECT MAX(RSV_DATE_TO) 
    INTO v_last_rsv_date 
    FROM HT_RESERVATION, 
       HT_ROOMS, 
       HT_STANDARDS, 
       HT_PRICES
    WHERE RSV_ROM_ID=ROM_ID 
    AND ROM_STD_ID=STD_ID 
    AND STD_ID=:new.PRC_STD_ID;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN 
      RETURN;
  END;
  --do skrocenia starej ceny
  v_temp_date_to:= :new.PRC_DATE_TO;
  --poprawna data dla nowej ceny to v_last_rsv_date
  :new.PRC_DATE_TO:= v_last_rsv_date;
  v_amount:=:new.PRC_AMOUNT;
  :new.PRC_AMOUNT:=:old.PRC_AMOUNT;
  v_std_id:=:new.PRC_STD_ID;
  :new.PRC_STD_ID:=:old.PRC_STD_ID;
  COMMIT;
  --skrocenie starej ceny w nowym rekordzie cenowym
  HP_MANAGING.ADD_PRICE(v_last_rsv_date, v_temp_date_to, v_std_id, v_amount);

END;

Any ideas how to fix it? When I'm trying to test it I'm getting logs:

Error starting at line 10 in command:
update HT_PRICES SET PRC_AMOUNT=501 where prc_id=1
Error report:
SQL Error: ORA-04091: table STD_USER.HT_PRICES is mutating, trigger/function may not see it ORA-06512: at "STD_USER.TRG_NEW_PRICE", line 8
ORA-04088: error during execution of trigger 'STD_USER.TRG_NEW_PRICE'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

First off, you should never be doing a commit in a trigger. Bad. If HP_MANAGING.ADD_PRICE is updating the same table that the trigger is on, that is a problem. — OldProgrammer, Mar 19 '17 at 19:28
Why does the query inside the trigger (which is line 8, in the error text) include HT_PRICES in the where clause - does it really need to look at that table again, rather than just the `new` pseudorecord? The query would be much easier to understand with proper modern join syntax, and with each column reference prefixed with the table it comes from (or an alias to that table). Plus what OldProgrammer said... — Alex Poole, Mar 19 '17 at 19:28
See [this answer](http://stackoverflow.com/a/29491264/213136) to [this question](http://stackoverflow.com/questions/29489951/oracle-trigger-after-insert-or-delete). — Bob Jarvis - Слава Україні, Mar 19 '17 at 20:49

score -1 · Answer 1 · answered Mar 19 '17 at 19:27

-1

1) Place your SELECT into a local procedure marked with pragma autonomous_transaction, this will allow select to run, though you won't see your current transaction uncommitted changes.

2) What is the reason to have COMMIT; in a BEFORE DML trigger?

answered Mar 19 '17 at 19:27

B Samedi

380
3
11

1

I may be mistaken, but pretty sure autonomous_transaction has no bearing on a select. – OldProgrammer Mar 19 '17 at 19:30
2

It would stop the mutating table error being thrown, but is a bad solution - as you said it won't see the current transaction, and is usually a bad hack to avoid this error. It's very rare for an autonomous transaction to be necessary. – Alex Poole Mar 19 '17 at 19:33
autonomous_transaction hides current transaction uncommitted changes, as if `SELECT` were run in a different session. – B Samedi Mar 19 '17 at 19:37

Insert inside the trigger in oracle

1 Answers1