| Author |
Topic |
|
Gilespy
Starting Member
3 Posts |
 Posted - 2006-03-06 : 14:05:57
|
| I have three tables (Professor, Topic and Organization (reference table which holds IDs of first two tables)).I need a solution to disallow any transaction which will allow more than two professors to teach one topic.I thing that ASTER INSERT, UPDATE trigger is the best solution, but I have never seen such trigger.Does someone have a solution? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-06 : 14:30:03
|
| I hate using triggers. Why not just put it in your stored procedures. Check for the existence of another professor teaching the topic, if it exists, don't perform insert. Otherwise, perform it.Tara Kizeraka tduggan |
 |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-06 : 14:36:26
|
| How about a check constraint and a UDF. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-06 : 15:48:57
|
| Are both professors completely equal in relation to the topic they teach together? If there are two of them teaching one topic, is there anything that distinquishes between the two? i.e., one is the primary, one is the associate, or something like that? It's like a business rule that states a customer can have up to two addresses. A better rule (both for the logical business model AND the database model) is that we store 1 address per type per customer, and the two types are "billing" and "shipping". |
|
|
|
Gilespy
Starting Member
3 Posts |
Posted - 2006-03-06 : 16:18:03
|
| Problem does not stated anything regarding relations between professors. So the easiest it gets the better it is.Tara, if I get you right, that way only one professor is allowed to teach a single topic. For that mater I can use UNIQUE constraint.Nevertheless, I already have a complete data base with all the procedures and .NET interface. So in order to implement your solution I need to break connections between SQL and interface.If I am wrong, please post a code of your solution (just the "check" part).Therefore I need the simplest solution I can get. Constraint over Organization table will do fine, but I never used such types of constraints (I need an example). Oh, yes. Organization table have Professor and Topic column, which are FK's. |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-06 : 16:30:08
|
| Just add a unique constraint to the Organization table so that Topic ID is unique. Then you will be not be able to have more than one Professor for a Topic.CODO ERGO SUM |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-06 : 16:33:38
|
quote:
I need a solution to disallow any transaction which will allow more than two professors to teach one topic.
Doesn't that mean there can be two professors per topic? |
|
|
|
Gilespy
Starting Member
3 Posts |
Posted - 2006-03-06 : 16:57:21
|
| Exactly! There can be two professors per topic, but not more than two.So every topic can be held by one or two professors. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-07 : 03:42:49
|
| simplest approach is to have this filtered in your sprocs as business rule2 professors = 1 topicset the rule on the topic table..if (select count(*) from topic where topicID=@topicID)<2 --add candidate professor to teach the topici'm probably oversimplifying this but hope this helps...--------------------keeping it simple... |
|
|
|
|
Constraint, trigger or something else