1

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;
  • Could you post the full trigger? That might help others to help you better. – RDFozz Aug 18 '17 at 14:59
  • I am not able to do that now but will do later today, thank you – DKCroat Aug 18 '17 at 15:23
  • 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. – EdStevens Aug 19 '17 at 15:18
  • I added wrong here when actually is the same data type. – DKCroat Aug 19 '17 at 16:48
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 
  • Thank you Stefan, I am going to look at it now. I wasn't sure what I was doing wrong. – DKCroat Aug 20 '17 at 12:32
  • 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 Aug 20 '17 at 13:23
  • 1 Snatch 36 2 Aliens -6 3 Mars -6 – DKCroat Aug 20 '17 at 13:26
  • Trigger code updated - please run some tests. (Will add another update to this answer later ...) – stefan Aug 21 '17 at 8:46
  • Thank you Stephen, I will check it out later, I do appreciate your time on this. – DKCroat Aug 21 '17 at 11:40
1

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

  • I have separate trigger created for incrementing and it works just fine however, it has been requested to create another trigger to handle decrement. – DKCroat Aug 18 '17 at 14:53
  • 1
    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. – MguerraTorres Aug 18 '17 at 14:57
  • 1
    That could be the case, I will try to add where clause and see what would happen, thank you – DKCroat Aug 18 '17 at 15:18
  • Sure thing. An easier approach to confirm would just be to disable the old trigger and see if the new one fires correctly. – MguerraTorres Aug 18 '17 at 15:18
  • 1
    I do agree procedures and functions are a lot more useful – DKCroat Aug 18 '17 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.