1 Replies - 7675 Views - Last Post: 22 May 2013 - 11:42 PM

#1 relics  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 31
  • Joined: 22-September 10

Trigger on insert or update

Posted 05 May 2013 - 02:31 PM

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.

This is the statement i have so far
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;



and this is the error that i am getting


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 am not sure how to do this without the sub query. Any ideas?

Is This A Good Question/Topic? 0
  • +

Replies To: Trigger on insert or update

#2 idunno  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-October 11

Re: Trigger on insert or update

Posted 22 May 2013 - 11:42 PM

CREATE OR REPLACE TRIGGER movietrigger

AFTER UPDATE OR INSERT
ON starsin  
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
DECLARE

    tmpvar NUMBER;
  BEGIN
    SELECT COUNT(1) INTO tmpvar FROM moviestar WHERE ROWNUM = 1 AND nam = :NEW.starname;
    
    IF(tmpvar= 0) THEN
      INSERT INTO moviestar(NAME) VALUES(:NEW.starname);  
    END IF; 

  END;



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1