How can I use multiple defaults in a table?

Posted on 2009-02-24
Medium Priority
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
  • 3
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23720883
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

ID: 23720954
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
ID: 23720964
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23720980
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

ID: 23721088
Thankyou angelll that worked perfectly!

Author Closing Comment

ID: 31550536

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

829 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