Possible to write this sql trigger?

Question

I have a sql statement to find the actors that have acted in the documentary film with the highest rental rate from the given schema:

film: film_id, rental_rate, 
film_actor: actor_id, film_id
film_category: film_id, category_id
category: category_id, name

SELECT *
FROM actor 
WHERE actor_id IN
        (SELECT actor_id
         FROM film_actor
         WHERE film_actor.actor_id = actor.actor_id
         AND film_id IN
                (SELECT film.film_id
                 FROM film, film_category, category
                 WHERE category.name = 'Documentary'
                 AND film.film_id = film_category.film_id
                 AND film_category.category_id = category.category_id
                 AND rental_rate = (SELECT MAX(rental_rate)
                                    FROM film, category, film_category
                                    WHERE category.name = 'Documentary'
                                    AND film.film_id = film_category.film_id
                                    AND film_category.category_id =  category.category_id)));

A question I've been asked to explain is whether it is possible to write a SQL statement to create an Oracle trigger that increases the rental_rate by 10% for any future films starring any of the actors that I found in the sql statement I've written. I can't really think of a reason why I couldn't - unless I have missed something but would like a second opinion about this, and how I could do it if possible

What should happen if the documentary film with the highest rental rate has been actually been miscategorized and its Documentary film category is deleted? What should happen if the rental rates of films are changed so the documentary film with the highest rental rate changes? What should happen if multiple actors appearing in the documentary film with the highest rental rate are associated singly with a future film? What if an actor appearing in the documentary film with the highest rental rate has been incorrectly associated with another film and the association is deleted? — DrabJay
– DrabJay, Commented Oct 5, 2014 at 20:21

Sylvain Leroux · Accepted Answer · 2014-10-06 09:48:44Z

1

Assuming the movie category will not change, you very probably could do that in one trigger only has you have the associative entity film_actor. Its key role is to implement the many-to-many relationship between film and actor.

But as a "side benefit", you know this table will be touched each time you add a new film with some actor, or each time you add an actor having played in at least one of your films. It will be touched as well is you somehow update the association between an existing film and an existing actor.

So using a trigger AFTER INSERT OR UPDATE on this table would do the trick.

edited Oct 6, 2014 at 9:48

answered Oct 5, 2014 at 18:42

Sylvain Leroux

52.4k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3603183 Over a year ago

A quick question though, how would you directly relate this to the change in rental_rate which is in the film table?

Sylvain Leroux Over a year ago

@user3603183 You are allowed to update film from a trigger on film_actor. So it shouldn't be an issue at first sight. The only complicated thing is probably to know if the target movie already has the +10% rental rate applied. Take a look ad OLD and NEW correalation names. They would allow you to query the DB in order to know if the bonus is already applied of not. Try to experiment a little on that, and if you still need some help, feel free to ask an other question.

user3603183 Over a year ago

Ahh okay, I will give that a go, thank you for answering the followup question

Collectives™ on Stack Overflow

Possible to write this sql trigger?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related