Oracle syntax error trigger

Question

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)));

possible duplicate of [if (select count(column) from table) > 0 then](http://stackoverflow.com/questions/10200281/if-select-countcolumn-from-table-0-then) — Jon Heller, Oct 07 '14 at 03:04
I've tried working with the solutions from the possible duplicate post, the same errors are being shown/thrown — user3603183, Oct 07 '14 at 03:34

score 2 · Accepted Answer · answered Oct 07 '14 at 05:55

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;

score 1 · Answer 2 · answered Oct 07 '14 at 05:52

1

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;

answered Oct 07 '14 at 05:52

Nitish

1,686
8
23
42

Oracle syntax error trigger

2 Answers2