Oracle SQL Trigger insert/update

Question

Ok so my question should be an easy one i think. I am just learning Triggers, and I am trying to figure out a homework question. I have three tables, Movies (title, year, length, genre, studioName, producer) StarsIn (movieTitle, starName) MovieStar (name, address, gender, birthdate)

So basically i need to write a trigger for assuring that at all times, any star appearing in StarsIn also appears in MovieStar. I need to make the trigger for both insert and update events.

UPDATE: Ok so i changed my statement a little but i still can't figure this out

CREATE OR REPLACE TRIGGER movieTrigger
  AFTER UPDATE OR INSERT ON STARSIN
  FOR EACH ROW
  WHEN(new.STARNAME NOT IN(SELECT "NAME" FROM MOVIESTAR))
  BEGIN
INSERT INTO MOVIESTAR("NAME")
VALUES(new.STARNAME)
END;

Now I am getting the error

Error report:
ORA-02251: subquery not allowed here
02251. 00000 -  "subquery not allowed here"
*Cause:    Subquery is not allowed here in the statement.
*Action:   Remove the subquery from the statement.

I just learned that oracle does not support a subquery in the when clause... So i am trying to figure this out with limited knowledge. But if anyone has a clever way of doing this i would really like to know :-).

Thanks again

Why wouldn't you just use a foreign key for this? There's no reason that this should ever be a trigger. — Scotch
– Scotch, Commented May 4, 2013 at 19:51
stackoverflow.com/questions/16404667/… This link should help, I had a similar problem.. — mingle
– mingle, Commented May 6, 2013 at 18:46
Just a side note: in your executable section (BEGIN..END) you need to refer to the columns using bind variable syntax, e.g. VALUES(:new.STARNAME). — Jeffrey Kemp
– Jeffrey Kemp, Commented May 8, 2013 at 1:55

Martin Drautzburg · Accepted Answer · 2013-05-04 19:52:49Z

1

You created a statement-level trigger. It will fire once for each insert or update statement. But a single insert or update statement can insert/update many rows in one go. Your code however needs a single row and assumes that only a single row is inserted or updated.

What you need are rowlevel triggers ("FOR EACH ROW") if you want to follow this path.

answered May 4, 2013 at 19:52

Martin Drautzburg

5,2731 gold badge29 silver badges40 bronze badges

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

Comments

Akash · Accepted Answer · 2013-05-04 19:49:45Z

0

Your trigger should be fired before(in this case) the insert of any row (currently it fires once for multiple rows inserted once)

I would recommend reading http://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adg13trg.htm

Its always better to use a foreign key constraint for ensuring that the value that appears in table b is present in table a, in this case there should be a foreign key in your table StarsIn for column starName referencing name in MovieStar table

answered May 4, 2013 at 19:49

Akash

5,02212 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Trigger insert/update

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