August 22, 2001 at 3:20 am
hi once again,
i am working on MS-SQL 7.0. Just Recently we have shifted our database from Access to SQL 7.0. I had normalised the database till 3 NF. Now I am having a field that takes up the office address. The value of the city, state and country comes from 3 different tables. We have given a drop down for the city field. Now when a data entry opertor enters a city and if it is not present then i want a trigger should fire. Then it should allow to enter the value of cityid and city. The cityid is autonumber. Then it will check if the corresponding state and country is present or not. If not then it should allow to enter and the corresponding triggers should fire. The stateid and countryid is also autonumber.
I think its a bit complex so any help from u guys will be highly appreciated.
Thanks
Keyur.
August 22, 2001 at 5:07 am
I dont think I'd put this in a trigger, depending on how good your UI is (and the # of times people access the table directly) you may wind up with a lot of junk (typo's, empty strings, wrong data entirely). A better approach is to limit the pick list to whats in the table, put an "add" button if the one they need is not there - then you can do some validation and tag is as new so that someone can review to insure good data. Same for state and country. Then use foreign key constraints to make sure it happens no matter how table is accessed. Remember to allow for instances where the item is not known!
Andy
August 22, 2001 at 9:42 am
I'm in agreement with Andy here. Basically, you have to consider that if you build the add a City into the UI, you're only running an operation in those specific cases where the City isn't present in the list. However, if you put the logic in a trigger, that trigger will fire for EVERY time you hit the table with INSERT/UPDATE depending on how you've defined your trigger. Why do the extra work?
K. Brian Kelley
K. Brian Kelley
@kbriankelley
August 22, 2001 at 2:22 pm
August 22, 2001 at 9:06 pm
I agree with Andy as well... but am I to understand that you have normalized the addresses to the point where the city of Greenville will have the same ID no matter if it is in South Carolina, California, or Kentucky? Or are you using this as an example?
August 22, 2001 at 10:10 pm
Thank u guys for ur help,, i will surely follow andy's suggestions and let u know
no the same city will have different ids if it is present in different states i.e. greenville will have different ids for different states it is present
thanks once again will let u know which suggestions click
bye, keyur
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply