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 |
|
sgnerd
Starting Member
6 Posts |
 Posted - 2003-06-04 : 09:51:02
|
| Dear Friends,Please help to solve the following problemI have one master table with a primary key filed (say 'emp_no). This emp_no is referred in other 5 tables (like FK).I dont want to delete a record in the mater table, if it's emp_no is referred in any of the child table.I have written my query in this wayDont deleteIF EXISTS (SELECT master.emp_no from master, tab1, tab2, tab3, tab4, tab5) WHERE tab1.emp_no = master.emp_no OR tab2.emp_no = master.emp_no OR tab3.emp_no = master.emp_no OR tab4.emp_no = master.emp_no OR tab5.emp_no = master.emp_no )but it doesnt work. Is this is right to proceed this way.Thanks in advance.RegardsKumar |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-04 : 10:07:10
|
When you say "It doesn't work", what do you mean ? Or do you want us to guess  Could it be the bit of code in place of "dont delete" that is wrong ?Another way around it, is rather than actually deleting records, have some field indicating the active or deleted status or the record and do updates rather than deletes.Damian |
 |
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2003-06-04 : 10:58:45
|
| If you don't want to delete a record from master table if there is any associated child rows in the child table, why don't you just establish Foreign Key relation between them instead of implementing the code? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-04 : 11:10:49
|
| I think he is trying to test before deleting so he doesn't get any errors.I guess you could also just give it a go and handle the error.Damian |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-04 : 12:26:15
|
quote:
Is this is right to proceed this way.
No. You reference (FK). Is there some reason you can't implement it. Is it a third party vendor product with no RI (hundred dollars it's peoplehard)...Brett8-) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-04 : 13:29:12
|
| Not very beautiful but this might workCREATE PROC DeleteMasterEmployee @EmpID INTIF EXISTS(SELECT emp_no FROM table1 WHERE emp_no = @EmpIDUNIONSELECT emp_no FROM table2 WHERE emp_no = @EmpIDUNIONSELECT emp_no FROM table3 WHERE emp_no = @EmpIDUNIONSELECT emp_no FROM table4 WHERE emp_no = @EmpIDUNIONSELECT emp_no FROM table5 WHERE emp_no = @EmpID)BEGIN --RAISE SOME ERRORS HERE!!!ENDOwais |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-04 : 14:05:29
|
Beauty, as they say, is in the eye of the beholder (or is that beer holder  )Brett8-) |
|
|
|
|
|
|
|
|
How to delete - Like FK delete