Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SqlStudent
Starting Member
1 Post |
 Posted - 2002-11-02 : 14:52:11
|
| Hi im in desperate need of help.i have three tables:Undergrad (reg_no, name, etc)Taught_PG (reg_no, name, etc)Research_PG (reg_no, name, etc)and a relationship scheme:Registered_for (reg_no, module_no)I know its not best way but ive been told to write a trigger that prevents a reg_no in Undergrad being a reg_no in Taugh_PG or Research_PG and so on. So the reg_no has to be unique across the three tables.Could some one please tell me how to do it? |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-02 : 20:40:41
|
| For starters, is Reg_no an identity column? If yes, good luck.If not: In an INSERT trigger on each table, check the rows in the Inserted table if any of the reg_nos exist in the other two tables, and if yes, ROLLBACK TRAN.If you would like more specific info, you may say so, but before you do, let me tell you that we on SQLTeam have a policy against doing homework assignments. If it is homework, you ought to be doing the research yourself.Sarah Berger MCSD |
 |
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2002-11-08 : 12:55:54
|
Nevermind triggers. They are slow.Make a new table called Reg_No (Reg_No, RegType). Reg_No is INT Identity, Type is INT with values (1,2,3). Then have your three tables each be on the many-side of a relationship with Reg_No. This way, Reg_No is a Foreign Key in each of those three tables. This enforces unique Reg_No's across all three.Then, make a SP to add new data to the tables so it is done in the right order, which is:INSERT INTO Reg_No (RegType) VALUES (@RegType)SELECT @NewRegNo = @@IDENTITYIF @RegType=1 THEN INSERT INTO Undergrad (@NewRegNo, @Name, @etc) VALUES (@NewRegNo, @Name, @Etc)ELSE IF @RegType=2 THEN INSERT INTO Taught_PG (@NewRegNo, @Name, @etc) VALUES (@NewRegNo, @Name, @Etc)ELSE IF @RegType=3 THEN INSERT INTO Research_PG (@NewRegNo, @Name, @etc) VALUES (@NewRegNo, @Name, @Etc) Take care,Edited by - smerrill on 11/08/2002 13:10:32 |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-08 : 13:42:09
|
| SMerril,Your code might get thrown off if there is another table using an Identity column.Sarah Berger MCSDEdited: I misunderstood SMerril's reply. He's actually right, and his code is good and will definitely work for the abovementioned scenario.Edited by - simondeutsch on 12/05/2002 11:34:57 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-12-05 : 15:35:57
|
quote:
Make a new table called Reg_No (Reg_No, RegType). Reg_No is INT Identity, Type is INT with values (1,2,3). Then have your three tables each be on the many-side of a relationship with Reg_No. This way, Reg_No is a Foreign Key in each of those three tables. This enforces unique Reg_No's across all three.
Shaun, I don't believe the Foreign Key definition will by itself enforce unique Reg_No's across all three tables. If someone were to, outside of your code snippet, insert a record directly into one of the tables, using a reg_no that already exists in another table (and in the master Reg_no table) then won't the insert succeed and break the business rules? Also, while your code snippet accomplishes the basic goal, it doesn't allow for multi-record inserts to be performed.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-05 : 16:01:15
|
| Can you explain why the Reg_no in each table has to be unique? This makes no sense to me. They are different tables!If they are sharing a set of Reg_no's, and are the same data elements, they should be in the same table, with Reg_no as a primary key.Maybe the answer is "they just have to be unique and in different unrelated tables" but I thought I'd ask anyway.- Jeff |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-12-06 : 03:25:09
|
quote:
Can you explain why the Reg_no in each table has to be unique? This makes no sense to me. They are different tables!If they are sharing a set of Reg_no's, and are the same data elements, they should be in the same table, with Reg_no as a primary key.
I would suggest it's because a student can only be one of Taught, Undergrad or Research types. I think that I'd agree it makes more sense for them to be in the same table - I can't imagine the differences at the student level being very great. But then I'd probably use first name and last name, too, instead of just "name". I guess when these questions are made up the lecturers are thinking about the specific problem rather than the database as a whole... (Odd really being as the start of a lot of database courses is to go into great depth about normalisation, then throw it out the window once another concept needs to be illustrated.)-------Moo. |
|
|
|
|
|
|
|
|
Bloody Triggers HELP ME!!!