SQL Trigger Question

[LLudden]

I have a table of city names that are referenced in another table by the primary key:
Table CityList, CityCode as int, City as nvarchar(25)

Other tables that use the CityCode to reference the city name, so to get an address for a customer record, I have to query the customer table for the city ID, then query the city table to get the city name. What I want to do is add a [CityName] field to my customer table to avoid this 2nd query. I did this with a query. Now I want to add triggers to provide for forward and backward compatibility.

Meaning, if I add (or update) a record with a program that uses CityCode, I want the trigger to update the CityName, and if I use a new program that updates the CityName, I need it to update the CityCode.

Any suggestions on the best way to implement this would be appreciated.

 

[TheBugSlayer]

Lludden,
keeping the CityName in the customer table in this case is against normalization rules as you already have a CityList table. You will be keeping the same data in two tables. But if it helps you, fine!

As for the trigger, you need to define one (FOR INSERT) on Customers table. According to what you're saying, depending on which program is used to insert in the table either CityCode or CityName will be empty. That is the condition you will be checking in the trigger. You will define two queries in it, one will retrieve CityName from the CityList table if CityName is null and the other one will insert CityCode if it is null.

It may happen that cities with different codes have the same name in which case you might need an additional filter to retrieve the CityCode given a CityNamr.

I hope this helps...