Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Unanswered: Need help making a trigger.

    I apologize in advance if this is the wrong board for this. I will move it if asked. The image attached is just a basic layout for the three tables im trying to pull info from.

    Maybe someone can help me. I am trying to make a simple sql database in Visual Studio 2013 for a class project. Three of my tables that my question address are in the picture.

    I have a fourth table that uses CustomerID, HIN, and BoatID as foreign keys to make a printable temporary docking tag with information from those three table.

    Here is what I'm trying to accomplish. I need the code for a trigger in the 4th table that when a user enters a BoatID from the BoatInfo table and a DockID from the DockInfo table it compares the HeightInFeet field from DockInfo with the
    HeightInFeet field from BoatInfo to make sure that the first is taller than the second and so the boat will fit in the dock. If the boat will not fit I want the database to reject that entry. An appropriate error message is not necessary for the
    purposes of the class but might make it look shinier if you want to throw that in too.

    I know this trigger is probably fairly simple to write but I am not very good in SQL and I just want this project over with so I can focus on important finals in classes that actually relate to my degree path. Click image for larger version. 

Name:	Test.jpg 
Views:	2 
Size:	58.7 KB 
ID:	16002

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I wouldn't write this in a trigger, but instead handle these rules in the procedure that performs the insert.

    P.S. when asking for help, it is best to show your workings so far i.e. what code have you written yourself as an attempt?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would have to disagree with gvee here. If you put the rules in the sproc that inserts data, then every insert and update will have to be done through that sproc or you will have to duplicate the logic in multiple pieces of code. This is a constraint on the data, so it should be kept as close to the data as possible.
    That means either a trigger (clumsy as it might be), or (possibly preferable) an Indexed View with a constraint.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fair enough Blindman.
    There are plenty of ways to skin this cat, each with their pros and cons!

    I suppose you could even create a check constraint that used a UDF for validation. *shrug*
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In my opinion, an ASSERTION would be the best solution if SQL Server supported the ASSERTION syntax from the ISO SQL standard.

    I haven't tested an indexed view with a CHECK CONSTRAINT, but that would be my next choice because it is completely declarative. This efficiently implements what an ASSERTION ought to do. I don't know if this works in any cases, but I know that it fails in all of the ones that I could easily check... I would test this carefully before I depended on it!

    A Trigger is procedural, but it works and does so in every version of SQL Server that I've ever used. While procedural code would drive a purist like Celko or Date mad, a trigger gets the job done and it can do so cleanly and dependably.

    -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
    and indexed views I have found to be onerous to implement and and troublesome in anything but ready only reporting databases.
    “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
  •