How can I use multiple defaults in a table?

Posted on 2009-02-24
Last Modified: 2012-05-06
I'm trying to do something which is very tricky to explain but hopefully someone will understand and be able to shine a light on the a situation for me.  
I am creating a database of medical professionals, I have 4 tables which are relevant to this problem.
Table1 -Contains all personal info for the person (EG FirstName, Surname etc) PK = PersonID

Table 2 -Contains a composite of foreign keys which colate all the detail needed for a persons occupation.
PK = PersonOccupationID, FK=PersonID, FK=OccupationID, FK=GradeTypeID

Table 3 -Contains a list of various generic Occupations a person in medicine can do (Eg Doctor, Nurse etc) PK=OccupationID

Table 4 -Contains a list of the different variations each of Table 3's Occupations can be. (Eg Doctor, can be GP or Hospital, Nurse, can be Community or Hospital) PK=GradeTypeID, FK=OccupationID

So you might have noticed that the OccupationID exists in the GradeType table.  I have done this because I was hoping that this structure would mean that if "nurse" was selected from the Occupation table then this would mean that in the GradeType table, only the GradeType fields with the relevant OccupationID for Nurse could be selected.

Basically I want to ensure that if Nurse is selected in Occupation then only the options relevant to that can be selected in GradeType. ie, A Nurse can only be Community, Hopital, MacMillan etc, and not GP which is only relevant to Doctor in Occupations.

Does that make sense?

Many thanks  

Question by:gd1982
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you will need a trigger that looks up if the inserted value OccupationID matches the value of occupationid in the table4.

    do you need help building such a trigger to rollback/error a insert/update if a wrong condition is filled in?

    note that this type of constraint should also be implemented in the client front end, ie limit the "drop-down of the GradeTypes based on the selected Occupation.

    Author Comment

    Yes thats brilliant.  I'm surprised you were able to understand my garbled explanation so well.  Any help building a trigger like that would be appreciated.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    You can achieve your requirement in Table 4 using any of the following approaches:

    1. Using A trigger on that column to update its values based upon Occupation
    2. Use a CASE LOGIC and do an insert or update in that table.

    You cant do a validation or Case logic while creating a default value for a table.
    LVL 142

    Accepted Solution

    let's see:
    CREATE TRIGGER trg_check_default
      ON yourtable2
                  FROM INSERTED i
                  JOIN table4 t4
                    ON t4.GradeID = i.GradeID
                   AND t4.OccupationID <> i.OccupationID 
         RAISERROR('Occupation/Grade mismatch', 16,1) WITH SETERROR 

    Open in new window


    Author Comment

    Thankyou angelll that worked perfectly!

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now