CODEWITHSUNDEEP
oracle-databasetriggers
user3603183
user3603183

Reputation: 333

Oracle syntax error trigger

I've been trying for over 2 hours to get the syntax right for this trigger, but it keeps giving me compilation errors, could someone please point me in the direction that I'm going wrong? 

[Compilation errors:  missing equal sign line 5, sql statement ignored, line 4]

film_actor has the following fields:

actor_id
film_id

film has the following fields:

film_id
rental_rate


CREATE OR REPLACE TRIGGER TRIGGER_ACTOR_STARRED
BEFORE INSERT ON film_actor FOR EACH ROW
BEGIN       
IF :new.actor_id IN (SELECT *
                    FROM V_ACTORS_STARRED) THEN
    UPDATE film 
    SET rental_rate := rental_rate * 1.10
    WHERE  :new.actor_id = film_actor.actor_id
    AND film.film_id = film_actor.film_id;
    END IF;
END;
/

The view I'm trying to select from is as follows:

CREATE OR REPLACE VIEW V_ACTORS_STARRED AS
SELECT actor_id
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)));

Upvotes: 1

Views: 198

Answers (2)

Mureinik
Mureinik

Reputation: 311123

You're executing an SQL update - it should use the regular = SQL operator, not pl/SQL's := assignment operator:

UPDATE film 
SET rental_rate = rental_rate * 1.10 -- Note ":=" was replaced with "="
WHERE  :new.actor_id = film_actor.actor_id
AND film.film_id = film_actor.film_id;

Upvotes: 2

Nitish
Nitish

Reputation: 1736

Remove the colon in the SET part of UPDATE statement.

UPDATE film 
SET rental_rate = rental_rate * 1.10
WHERE  :new.actor_id = film_actor.actor_id
AND film.film_id = film_actor.film_id;

Upvotes: 1

Related Questions