SQL Movie-Rating View Modification Exercises Part A

SQL Movie-Rating View Modification Exercises

Because of the length of the post this includes Questions 1-5.

Question 1

Write an instead-of trigger that enables updates to the title attribute of view LateRating.

create trigger Q1
instead of update of title on LateRating
for each row
begin
   update Movie
   set title = New.title
   where title = Old.title and
     New.mID in (Select mID from Movie);
end;

Question 2

Write an instead-of trigger that enables updates to the stars attribute of view LateRating. 

create trigger Q2
instead of update of stars on LateRating
for each row
when  New.mID in (select mID from Rating where ratingDate = New.ratingDate)
begin
   update Rating
   set stars = New.stars
   where   Old.ratingDate = ratingDate  AND
     Old.mID = mID and Old.stars = stars;
end;

Question 3

Write an instead-of trigger that enables updates to the mID attribute of view LateRating. 

create trigger Q3
instead of update of mID on LateRating
for each row
when  Old.mID in (select mID from Rating)
begin
   update Movie
   set mID = New.mID
   where  Old.mID = mID ;
   update Rating
   set mID = New.mID
   where  Old.mID = mID ;
end;

 

Question 4

Finally, write a single instead-of trigger that combines all three of the previous triggers to enable simultaneous updates to attributes mID, title, and/or stars in view LateRating. Combine the view-update policies of the three previous problems, with the exception that mID may now be updated. Make sure the ratingDate attribute of view LateRating has not also been updated -- if it has been updated, don't make any changes.

create trigger Q4
instead of update on LateRating
for each row
when  Old.mID in (select mID from Rating where ratingDate = New.ratingDate)
begin
  update Movie
    set  title = New.title, mID= New.mID
    where Old.mID = mID;

 update Rating
   set  stars = New.stars
   where  Old.mID = mID and ratingDate = old.ratingDate;
 

 update Rating
   set mID = New.mID
   where  Old.mID = mID  ;
end;

Question 5

Write an instead-of trigger that enables deletions from view HighlyRated. 

create trigger Q5
instead of delete on HighlyRated
for each row
begin
  delete from Rating
  where mID = Old.mID
  and stars > 3;
end;

 ....to be continued...