Oracle PL/SQL Trigger to update a outstanding field on one table when another table is updated with date

Question

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;

Answer 1

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 -> 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> 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> select count(*) from rentals;
COUNT(*)  
84  

SQL> 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> 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> select * from movies;
MID  MOVIENAME  OUTSTANDING  
1    Snatch     36           
2    Aliens     42           
3    Mars       0 

Answer 2

This sounds like it would be better off in the "RentMovie" procedure vs. a trigger. Those can get a bit taxing if they're done too often.

So the "RentMovie" procedure would:

• Populate Rental.Rent_In
• Reduce the value of movie.Outstanding by 1

Or you can just separate the movie.Outstanding update into its own Procedure and call it that way instead.

Note that the same procedure would be able to handle the increment of the value as well (in the reverse scenario).