CODEWITHSUNDEEP
oracle-databaseplsqlsql-insertdatabase-trigger
Noob Coder
Noob Coder

Reputation: 21

Writing complicated row level trigger In Oracle

I have to create a row level trigger on a table called Movies that will compute the five greatest movie ratings from this Movies table and insert a new table called TopMovies with these movies. Each time I put a new rating in the Movies table this trigger fires. I am really struggling to understand how to go about this.

The Movies table has the following attributes (Movie, Director, Rating) The Top Movies table will have (Rating)

So far I have just the following code. I really am lost as to how to make it so that once I have inserted at least 5 ratings into the movies table, the TopMovies table will delete the lowest rated movie and re-populate it with a new movie. I am not asking for the answer but any help to go in the right direction

Create of replace Trigger top_trigger
After insert or update on Movies
For each row

Begin
   insert into TopMovies values (:new.rating),
End

Upvotes: 0

Views: 161

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You may try below code -

Create of replace Trigger top_trigger
After insert or update on Movies
Declare
    v_movie_count  number:= 0;
Begin
    select count(*)
    into v_movie_count
    from Movies;

    if v_movie_count >= 5 then

       delete from TopMovies;

       INSERT INTO TopMovies
       SELECT *
       FROM (SELECT Rating, ROWNUM RN
             FROM Movies
             ORDER BY Rating)
       WHERE RN <= 5;
       /* You can use below statement if you are using 12C or higher*/
       /*INSERT INTO TopMovies
         SELECT Rating, ROWNUM RN
         FROM Movies
         ORDER BY Rating DESC
         FETCH FIRST 5 ROWS ONLY;*/
    end if;
End

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use the following triggers. But be aware that concurrent requests may change the expected behavior when you use the triggers.

Trigger solution:

CREATE OR REPLACE TRIGGER TOP_TRIGGER AFTER
    INSERT OR UPDATE ON MOVIES
    FOR EACH ROW
DECLARE
    LV_CNT      NUMBER := 0;
    LV_RATING   TOPMOVIES.RATING%TYPE;
BEGIN
    SELECT COUNT(1), MIN(RATING)
      INTO LV_CNT, LV_RATING
      FROM TOPMOVIES;

    IF LV_CNT >= 5 AND :NEW.RATING > LV_RATING THEN
        DELETE FROM TOPMOVIES WHERE RATING = LV_RATING;
    END IF;

    IF LV_CNT < 5 OR ( LV_CNT >= 5 AND :NEW.RATING > LV_RATING ) THEN
        INSERT INTO TOPMOVIES VALUES ( :NEW.RATING );
    END IF;

END;
/

The better solution is to use the view.

VIEW solution:

CREATE OR REPLACE VIEW TOPMOVIES_VW AS
    SELECT * FROM
        (
            SELECT T.*,
                   DENSE_RANK() OVER( ORDER BY RATING DESC) AS RANK#
              FROM MOVIES T
        )
    WHERE RANK# <= 5;

Cheers!!

Upvotes: 2

Related Questions