So I have example 2 tables:
mid- pk varchar2
sid- num pk
mid- fk varchar2
Need to have trigger that would decrement value in
rental.rent_in is populated.
I tried to write something like this in trigger
outstanding = outstanding - 1 when mid = old.mid
but when trigger is fired it increase number instead to decrease. I used for each row and after update. Need help with this please.
So my first trigger to update rental table is:
create or replace trigger rentals_rent_trg after insert on rentals for each row begin update Movies set outstanding = outstanding + 1 where mid = :new.mid; end;
Please I need help with 2nd trigger. This is my 2nd trigger, it is not correct, I have tried few things but is not working:
create or replace trigger rentals_return_trg after update of return_date on rentals for each row begin update Movies set outstanding = outstanding - 1 where :old.mid = :old.mid; end;
I have tried to write 2nd trigger like this and then I am receiving ORA-04091....RENTALS is mutating, trigger/function may not see it
create or replace trigger rentals_return_trg after update of return_date on rentals for each row declare return_date_temp date; begin select return_date into return_date_temp from rentals where return_date = :new.return_date and return_date is not null; if return_date_temp is not null then update Movies set outstanding = outstanding - 1 where mid = :new.mid ; /*mid = :old.mid;*/ end if; end;
Since I was getting that error I have created compund trigger, however now I managed to start decreasing number however instead to be based on rows it is based on run I belive since it lowers only for one digit down if :old.outstanding = 5 then :new.outstanding = 4. I really need HELP please hahaha.
create or replace trigger rentals_return_trg FOR update of return_date on rentals COMPOUND TRIGGER cursor return_date_cur is select R.rid, R.mid, R.return_date, M.outstanding from rentals R JOIN MOVIES M ON R.mid = M.mid; type return_typ is table of return_date_cur%ROWTYPE index by binary_integer; return_tbl return_typ; INT number(4) := 0; BEFORE STATEMENT IS begin for rec in return_date_cur loop int := int +1; return_tbl(int).rid := rec.rid; return_tbl(int).mid := rec.mid; return_tbl(int).return_date := rec.return_date; return_tbl(int).outstanding := rec.outstanding; end loop; END BEFORE STATEMENT; AFTER EACH ROW IS LV_RETURN_DT DATE; LV_OUTSTANDING_NUM NUMBER; BEGIN for i IN 1..return_tbl.count loop if return_tbl(i).return_date = :NEW.return_date THEN LV_OUTSTANDING_NUM := return_tbl(i).outstanding - i; exit; end if; end loop; if :new.return_date is not null then update movies set outstanding = LV_OUTSTANDING_NUM; end if; end after each row; end;