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 — Jon Heller
– Jon Heller, Commented Oct 7, 2014 at 3:04
I've tried working with the solutions from the possible duplicate post, the same errors are being shown/thrown — user3603183
– user3603183, Commented Oct 7, 2014 at 3:34

Mureinik · Accepted Answer · 2014-10-07 05:55:05Z

2

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;

answered Oct 7, 2014 at 5:55

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

Nitish · Accepted Answer · 2014-10-07 05:52:42Z

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 7, 2014 at 5:52

Nitish

1,7568 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Oracle syntax error trigger

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related