Oracle PL/SQL Trigger to update a outstanding field on one table when another table is updated with date So I have example 2 tables: 

movies:


mid - pk varchar2
MovieName - varchar2
outstanding - num


rental:


sid - num pk
mid - fk varchar2
rent_out - date
rent_in - date


Need to have trigger that would decrement value in movies.outstanding when 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;
Using Oracle 12c, I've tried to re-trace your steps.  Your second trigger needs some modifications ...

Test setup:

create table movies (
  mid varchar2(64) primary key
, MovieName varchar2(64)
, outstanding number
);

create table rentals(
  sid number primary key
, mid varchar2(64) references movies(mid)
, rent_out date
, rent_in date
) ;

-- original trigger -&gt; okay, works for INSERTs 
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;
/


Test data (inserts):

insert into movies (mid, moviename, outstanding)
values (1, 'Snatch', 0);
insert into movies (mid, moviename, outstanding)
values (2, 'Aliens', 0);
insert into movies (mid, moviename, outstanding)
values (3, 'Mars', 0);

-- check
select * from movies;

SQL&gt; select * from movies;
MID  MOVIENAME  OUTSTANDING  
1    Snatch     0            
2    Aliens     0            
3    Mars       0

-- table RENTALS: insert 42 rows for movies 1 and 2
begin
  for i in 1 .. 42 
  loop
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+100,1,'19-AUG-2017',null); 
    insert into rentals (sid, mid, rent_out, rent_in)
    values (i+200,2,'20-AUG-2017',null); 
  end loop;
end;
/

-- checks
SQL&gt; select count(*) from rentals;
COUNT(*)  
84  

SQL&gt; select * from movies;
MID  MOVIENAME  OUTSTANDING  
1    Snatch     42           
2    Aliens     42           
3    Mars       0  


Let's use your second trigger, with minor modifications:

create or replace 
  trigger rentals_return_trg 
  after update of rent_in on rentals
  for each row 
declare
  new_ number default 0 ;
begin
  new_ := :new.mid ;

  update Movies
  set outstanding = outstanding - 1
  where mid = new_;

end;
/

Trigger RENTALS_RETURN_TRG compiled


Now, if we UPDATE some of the rows in the "rentals" table, and have a look at "movies" and "rentals", respectively, everything seems to be working as you wanted it to work (- only the affected rows are SELECTed from rentals in this example).

-- "Aliens" and "Mars" not affected
begin
  for i in 120 .. 125
  loop
    update rentals
    set rent_in = '22-AUG-2017'
    where sid = i ;    
  end loop;
end;
/   

SQL&gt; select * from rentals where rent_in is not null; 
SID  MID  RENT_OUT   RENT_IN    
120  1    19-AUG-17  22-AUG-17  
121  1    19-AUG-17  22-AUG-17  
122  1    19-AUG-17  22-AUG-17  
123  1    19-AUG-17  22-AUG-17  
124  1    19-AUG-17  22-AUG-17  
125  1    19-AUG-17  22-AUG-17 

SQL&gt; select * from movies;
MID  MOVIENAME  OUTSTANDING  
1    Snatch     36           
2    Aliens     42           
3    Mars       0