You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process, each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus, referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do?
A.
Drop the foreign key. Import the data by using the script. Re-create the foreign key.
B.
Create a CHECK constraint.
C.
Create a DML INSTEAD OF trigger that writes the failed records to a file or table.
D.
Create a DML AFTER trigger that writes the failed records to a file or table.
Explanation:
A DML INSTEAD OF trigger causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire, meaning that correctly used this will write a record missing the CountryCode to an alternative destination and not stop the import.