CODEWITHSUNDEEP
sqldatabaseoracle-database
mingle
mingle

Reputation: 620

Oracle Trigger Insert/update

Ok so i am working on a homework assignment. I have three tables, Movies (title, year, length, genre, studioName, producer) StarsIn (movieTitle, starName) MovieStar (name, address, gender, birthdate)

Basically i need to make sure when someone inserts or updates something in StarsIn those changes need to happen in MovieStar also.

So far i have something like this..

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

I am getting a few compiler errors

Error(4,1): PL/SQL: SQL Statement ignored
Error(4,28): PL/SQL: ORA-00933: SQL command not properly ended
Error(5,10): PLS-00103: Encountered the symbol "ROW" when expecting one of 
the following: in

I am very new to oracle and I am practicing Triggers. I know that this could be easily done using foreign keys, but the assignment is to use triggers.

I could really use some help with this. I have tried like a million different ways to make this happen, with no luck.

Thanks in advance for any help/advice.

Upvotes: 0

Views: 305

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

I'd suggest specifying the trigger fire for each row. I find these a lot easier.

You can do a count to see if the MovieStar.Name value already exists and then insert if it doesn't; that's similar to the approach you have above. This will fail if another user inserts the movie star between the time you check and the time you insert, but it's probably good enough for a class assignment. There are accepted no-fail approaches for this but you may not have covered them in class yet.

Try something like this; it probably incorporates everything you've covered in class so far:

CREATE OR REPLACE TRIGGER TestTrig
AFTER UPDATE OR INSERT ON STARSIN
FOR EACH ROW
DECLARE
  movieStarCount NUMBER;
BEGIN
  SELECT COUNT(*) INTO movieStarCount
    FROM MovieStar
    WHERE Name = :NEW.StarName;
  IF movieStarCount = 0 THEN
    INSERT INTO MovieStar (Name) VALUES (:NEW.StarName);
  END IF;
END;

Upvotes: 1

Related Questions