CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Long Huynh
Long Huynh

Reputation: 1

PLS-00103: Encountered the symbol "SELECT" oracle sql developer

So I got these as my tables

Member(member_ID first_name, last_name)

Profile(member_ID profile_name) 

Movie(movie_ID title, movie_year, producer, avg_rating)

Actor(actor_ID, first_name, last_name) 

Starred_By(movie_ID, actor_ID 

Watch(member_ID. profile_name, movie_ID rating)

For each relation, the attribute(s) of the primary key is(are) underlined. In addition, the following foreign key constraints hold:

In Profile,

Foreign Key: member_ID references Member(member_ID)

In Starred_By,

Foreign Key: movie_ID references Movie(movie_ID) 
Foreign Key: actor_ID references Actor(actor_ID)

In Watch,

Foreign Key: (member_ID, profile_name) references Profile(member_ID, profile_name) 
Foreign Key: movie_ID references Movie(movie_ID)

My assignment was: Write a trigger that updates the attribute “avg_rating” automatically whenever a new tuple is inserted into Watch. I came up with this but there was an error while compiling saying

PLS-00103: Encountered the symbol "SELECT"

create trigger trig1
    after insert on Watch
    for each row
    when (new.movie_ID is not null)
    begin
        update Movie
        set ava_rating = (select avg(W.rating)
                          from Watch W
                          where W.movie_ID = new.movie_ID);
    end;

Upvotes: 0

Views: 98

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Pseudorecord is to be addressed with a colon sign (:new instead of just new) in trigger body (unlike the when clause, where you did it right):

SQL> create trigger trig1
  2      after insert on Watch
  3      for each row
  4      when (new.movie_ID is not null)
  5      begin
  6          update Movie
  7          set ava_rating = (select avg(W.rating)
  8                            from Watch W
  9                            where W.movie_ID = :new.movie_ID);
 10      end;
 11  /

Trigger created.

SQL>

Upvotes: 1

Related Questions