Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: Can anyone help me write this database trigger?

    DB schema is as follows:

    Movie(title, year, length, incolor, studioName, producerC#)
    StarsIn(movieTitle, movieyear, starName)
    MovieStar (name, address, gender, birthdate)
    MovieExec (name, address, cert#, networth)
    Studio (name, address, presC#)

    Write a trigger. In each case, disallow or undo the modification if it does not satisfy the stated constraint.
    You may assume that the desired condition holds before any change to the
    database is attempted. Also, prefer to modify the database, eyen if it means
    inserting tuples with NULL or default values, rather than rejecting the attempted modification

    1. Assure that at all times every mo~iex ecutive appears as either a studio president, a producer of a movie: or both.
    2. Assure that the number of movies made by any studio in any year is no more than 100.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, we are not going to do your homework for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Awww but blindman!
    *goes off in a huff*
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by georgev
    Awww but blindman!
    *goes off in a huff*

    ROFL georgev

    I am so glad I didnt ask my backup question, got it figured out now

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    someone will be happy to help if you show your work. what did you try? what are you confused about?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) Impossible - tuples cannot be inserted into RDBMSs
    2)
    Code:
    CREATE FUNCTION dbo.countywountydodah
        (
              @studioName    VARCHAR(100)
            , @year            INT
        )
        RETURNS TINYINT
    AS
    BEGIN
        RETURN (SELECT    COUNT(*)
                FROM    Movie
                WHERE    studioName    = @studioName
                        AND [year]    = @year)
    END
    GO
    
    ALTER TABLE Movie 
    ADD CONSTRAINT checkyweckywoo CHECK (100 >= dbo.countywountydodah(studioName, [year]))
    GO

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Code:
    ADD CONSTRAINT checkyweckywoo
    Geek comedy gold!
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    1) Impossible - tuples cannot be inserted into RDBMSs.
    Bullcrap. Even a db-noob can do this:
    insert into tbl_RDBMS (str_Data) Select Tuple from tbl_Jargon
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Jeeze guys, cut the poor kid some slack. This would be a lot harder to solve if not for the direction given in the assignment:
    Quote Originally Posted by assignment
    Also, prefer to modify the database, eyen if it means
    inserting tuples with NULL or default values, rather than rejecting the attempted modification
    Here is a full-blown NZDF solution to get the kid started on their own solution:
    Code:
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    GO
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    CREATE TRIGGER tiMovie
    ON Movie
    FOR INSERT
    AS
    
    UPDATE Movie
       SET [producerC#] = NULL		== #1
    
    IF 100 < (SELECT Count(*)
       FROM Movie
       WHERE  Movie.studioName = inserted.studioName)
       UPDATE Movie
          SET studioName = NULL		-- #2
          WHERE Movie.studioName = inserted.studioName
    
    RETURN
    GO
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    CREATE TRIGGER tuMovie
    ON Movie
    FOR UPDATE
    AS
    
    IF Updated([producerC#])
       UPDATE Movie
          SET [producerC#] = NULL		-- #1
    
    IF 100 < (SELECT Count(*)
       FROM Movie
       WHERE  Movie.studioName = inserted.studioName)
       UPDATE Movie
          SET studioName = NULL		-- #2
          WHERE Movie.studioName = inserted.studioName
    
    RETURN
    GO
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    CREATE TRIGGER tdMovie
    ON Movie
    FOR DELETE
    AS
    
    UPDATE MovieExec
       SET [cert#] = NULL			-- #1
    
    RETURN
    GO
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    CREATE TRIGGER tiMovieExec
    ON MovieExec
    FOR INSERT
    AS
    
    UPDATE MovieExec
       SET [presC#] = NULL			-- #1
    
    RETURN
    GO
    --  ptp  20080617  NZDF solution for http://www.dbforums.com/showthread.php?t=1631357
    
    CREATE TRIGGER tuMovieExec
    ON MovieExec
    FOR UDPATE
    AS
    
    IF Updated([presC#])
       UPDATE MovieExec
          SET [presC#] = NULL		-- #1
    
    RETURN
    GO
    -PatP

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am naming at least one database object with pootles pillow talk naming convention tomorrow.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    I am naming at least one database object with pootles pillow talk naming convention tomorrow.
    I tell you - sweet talk that baby and she'll purr along - no more blocking or pesky exclusive table locks.

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by pootle flump
    I tell you - sweet talk that baby and she'll purr along - no more blocking or pesky exclusive table locks.
    maybe at first.

    before you know it she'll be lying on the couch all day, fat, slow, using up all your resources.

Posting Permissions

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