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;

• RDFozz: Could you post the full trigger? That might help others to help you better.
• DKCroat: I am not able to do that now but will do later today, thank you
• EdStevens: Just a side observation: RENTAL has a column MID which you indicate as FK. I'd guess the FK references MOVIES.MID, but the data types do not match. That is a serious design flaw, forcing oracle to make implicit conversions that are sure to eventually come across data that cannot be converted properly. Never depend on implicit conversions.
• DKCroat: I added wrong here when actually is the same data type.

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 

• DKCroat: Thank you Stefan, I am going to look at it now. I wasn't sure what I was doing wrong.
• DKCroat: Stefen, if I have more then one movie then it would update all list see below, something is missing maybe in condition to stop updating movies in Movies table that are not updated in rental
• DKCroat: 1 Snatch 36 2 Aliens -6 3 Mars -6
• stefan: Trigger code updated - please run some tests. (Will add another update to this answer later ...)
• DKCroat: Thank you Stephen, I will check it out later, I do appreciate your time on this.

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).

• DKCroat: I have separate trigger created for incrementing and it works just fine however, it has been requested to create another trigger to handle decrement.
• SQLDevDBA: Then it seems like your first trigger is the one being fired instead of your new trigger that you're trying to implement. I would change the old trigger to be more specific by adding a where clause which excludes the scenario you're trying to catch in your new trigger. I have to say that I would highly suggest you take this functionality out of triggers and add them into procedures, as the requests will keep coming.
• DKCroat: That could be the case, I will try to add where clause and see what would happen, thank you
• SQLDevDBA: Sure thing. An easier approach to confirm would just be to disable the old trigger and see if the new one fires correctly.
• DKCroat: I do agree procedures and functions are a lot more useful