Use UPDATE triggers to maintain data integrity

I have the following two tables in my database:

The CountryName field in the StateProvince table is redundant, and must be updated whenever the CountryName field is updated in the Country table.

For the sake of this example, it is a requirement that the structure of these two tables is not altered, because there is a large application which interacts with this database that would cost $$$$ to alter.

One way to address this problem, is to write an UPDATE trigger like the following:

CREATE TRIGGER UpdateCountry ON Country
FOR UPDATE
AS
BEGIN
    DECLARE @sOldCountryName VARCHAR(50)
    DECLARE @sNewCountryName VARCHAR(50)

    SELECT @sOldCountryName = CountryName FROM DELETED
    SELECT @sNewCountryName = CountryName FROM INSERTED

    UPDATE StateProvince
      SET CountryName=@sNewCountryName
    WHERE CountryName=@sOldCountryName
END

Well, let's see if it works.

I have the following records in my Country table:

...and the following records in my StateProvince table:

Now, I couldn't help noticing that the name of one of the countries on my list, has been misspelled, so I'd better update it. Most normal people would probably do that through the enterprise manager, but I'll do it by executing the following SQL command:

UPDATE Country
SET CountryName='United States of Amerrica'
WHERE CountryID='US' 

The trigger I wrote has correctly updated my StateProvince table so that it contains:

So far, so good.

Now, it turns out that I have several databases which store information on different countries, and I have developed a standard set of names for each country. So that for example, "China" is referred to as "The People's Republic of China".

I have put this standard list of country names in a table called StandardCountryNames:

To update my Country table with these names, I have executed the following query:

UPDATE Country
SET CountryName=StandardCountryNames.CountryName
FROM Country INNER JOIN StandardCountryNames
ON Country.CountryID=StandardCountryNames.CountryID

This UPDATE, has updated two records in the Country table, so that it now contains:

The StateProvince table now contains:

If you are skimming through this article, look at the above table again. Of the four records shown, two have been updated, and two have not.

Why?

The UPDATE trigger, is only executed once per UPDATE command. It is not executed for each individual record that is updated.

The second version of my UPDATE trigger is as follows:

CREATE TRIGGER UpdateCountry ON Country
FOR UPDATE
AS
BEGIN
    UPDATE StateProvince
      SET CountryName=INSERTED.CountryName
    FROM StateProvince INNER JOIN INSERTED
      ON StateProvince.CountryID=Inserted.CountryID
END

This trigger will work correctly, if the CountryID field is not being updated.

If the CountryID field is being updated, the JOIN condition must be ON some other unique field that is not being updated.

If the CountryName field is unique, and if it will never be updated at the same time as the CountryID field, then the following trigger will check whether the CountryID field is being updated and act accordingly:

CREATE TRIGGER UpdateCountry ON Country
FOR UPDATE
AS
BEGIN
    IF Update(CountryID)
        UPDATE StateProvince
          SET CountryID=INSERTED.CountryID
        FROM StateProvince INNER JOIN INSERTED
          ON StateProvince.CountryName=Inserted.CountryName
    ELSE
        UPDATE StateProvince
          SET CountryName=INSERTED.CountryName
        FROM StateProvince INNER JOIN INSERTED
          ON StateProvince.CountryID=Inserted.CountryID
END

What if the CountryID field is updated at exactly the same time as the CountryName field, as in the following UPDATE command?

UPDATE Country
SET CountryID='AUS',
CountryName='The great proud land of Australia'
WHERE CountryID='AU'

This represents a problem which cannot be solved without modifying the structure of the database, because within the trigger, there is no way of linking the field values on the updated record with the previous field values.

The only way to accommodate this possibility, is to add another unique key, one that will never be updated.

...And that is why it is nearly always a good
idea for every table to have an IDENTITY field.

Adelle.