Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: Urgent Help: SQL Trigger

    Hi

    I've been trying to solve the following problem for a few days but everytime I get the error msg even when the entered value doesn't conflict with the statement. Plz I need an urgent help

    I have the following tables:

    booking
    bookingId (PK)
    subjectId (FK)

    Subject
    subjectId (PK)
    termId

    I want to create a trigger to check the inserted values in booking table . If there's another subject have the same termId as the inserted one I should get an error msg. I tried to do the following but it didn't work



    Code:
          CREATE TRIGGER tr_check ON
     
          booking
    
          FOR INSERT, UPDATE
      
          AS
      
          BEGIN
      
          IF EXISTS( SELECT b.bookingId FROM
       
          booking b, inserted i, subject s1, subject s2, subject s
      
           
     
          WHERE
    .
          b.subjectId=s1.subjectId AND
      
          i.subjectId=s2.subjectId AND
    
          s1.subjectId=s.subjectId AND
      
          s2.offeringId=s.subjectId AND
     
          s1.termId=s2.termId )
     
          Raiserror('ERROR',10,4)
    
          ROLLBACK TRANSACTION
    
          END
    Thanx

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't use a trigger, declare a PRIMARY KEY instead.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2010
    Posts
    2
    But i'm asked to do it using a trigger for insert, update. Plz any help would be appreciated

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Start by rewriting your query using JOIN syntax please, and then I'll take a look at it for you.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you explained why one trigger can not do what is necessary? Just the need to write and maintain two (or more) triggers versus a single PRIMARY KEY declaration ought to be enough to convince them that this is a bad idea.

    There are only two reasons I can imagine that would justify using triggers for DRI. The first reason would be if you were using a database engine that supported triggers but did not support DRI, but I haven't seen one of those in years. The other reason would be a a learning exercise for students that were just starting to learn about databases, so that they could discover WHY it is so important to use DRI within a database product.

    I can't imagine any business reason for using a trigger instead of DRI.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or you could be maintaining RI between 2 tables in 2 databases that are in seperate database so you can split up your replication work load or some hair brained the scheme the former head of the dbas is carrying out in dev around here or something.
    “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.

Posting Permissions

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