Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: Oracle newbie Kim

    Hello, I am working on a database which is designed for dvds, two tables which are directors and movies are joined together by the foreign key director_No. However I would like to add a clause so that in the database a director has to have at least one movie. I have tried to implement this using triggers and procedures but have failed.

    I made the code below but it doesn't work and I also had another procedure which inserted data into the database and if a director had no movies the director would be removed. Any help/advice would be great. Thank you Kimmi

    CREATE OR REPLACE PROCEDURE count_MOVIES(dir_no IN INTEGER, count_test OUT INTEGER, min_no OUT INTEGER)
    IS
    min_no := 1;
    BEGIN

    SELECT COUNT(*)
    INTO count_test
    FROM movie
    WHERE director_no = dir_no;

    end;


    CREATE OR REPLACE TRIGGER movie_needed
    BEFORE INSERT
    ON movie
    FOR EACH ROW
    DECLARE
    dir integer;
    min_no_movies integer;
    BEGIN

    count_movies(:NEW.director_no, dir, min_no_movies);

    IF dir < min_no_movies THEN
    RAISE_APPLICATION_ERROR(-20000,'error1');
    END IF;
    END;
    /

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am not sure why you want to enforce such a silly rule, how are you every going to be able to enter a movie for a new director without adding the director record first. However if after all the movies and directoriers are added in and you want to perform cleanup, then do the following


    delete directors a
    where not exists
    (select null
    from movies b
    where a.director_no = b.director_no);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, why would you want to delete directors? If you delete all his movies, are you sure you won't have another one soon? This would require you to enter all his data again. And again. And again. Space requirements certainly shouldn't be a problem so - I'm just interested - why?

    This (deleting directors if their movies are deleted) can be done your way; procedure code could be moved into the trigger code to simplify the whole thing, but that would finally lead you to a mutating table error which, of course, can be solved, but the question is: is this worth all the pain?

    To me, it seems that your model is twisted: movies are parents and directors are children. I think that it would be more logical if it was opposite - every director can have one or many movies, while every movie has one director.

    The code you posted doesn't make much sense; it doesn't follow your theory which says that "no director can exist without at least one movie". Trigger checks whether a NEW inserted director has records in the database. Why would you do that? There is always a "first time" when a director is inserted (with his/her first movie in the database).

    Therefore, I'd suggest you to check documentation about referential constraints, especially the ON DELETE CASCADE option. Shortly, the ON DELETE CASCADE clause tells Oracle to delete the child tables when the corresponding parent table row is deleted. If the ON DELETE CASCADE clause is left off, Oracle will not allow the deletion of a parent row unless all the child rows are deleted first.

    This would allow deleting all movies of a certain director in "my" model, using two simple CREATE TABLE statements and no additional coding, such as:
    Code:
    CREATE TABLE DIRECTOR 
    (dir_id   NUMBER PRIMARY KEY, 
     dir_name VARCHAR2(10)
    );
    
    CREATE TABLE MOVIE 
    (mov_id   NUMBER PRIMARY KEY, 
     mov_NAME VARCHAR2(10), 
     dir_id   NUMBER CONSTRAINT fk_dir 
                     REFERENCES DIRECTOR (dir_id) 
    				 ON DELETE CASCADE
    );
    Now, this is perhaps not what you really wanted, but you might consider such an idea.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Every movie can have 1 or MORE directors.
    You should account for this possibility.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •