You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. As the administrator you plan to import client information from the Client table and the Product table in your SQL Server 2000 database into a SQL Server 2005 database. You have received instruction from the CIO to make certain that every value in the ProductCode column of the Client table has a corresponding record in the ProductCode column in the Product table of the SQL Server 2005 database during the import process.
You need to identify a foreign key between these two tables. Referential integrity will ensure that the import routine fails if there are any records where a ProductCode value does not exist in the Product table but does exist in the Client table. You need to make sure that the import process does not fail if it comes across records that do not exist in the Product table.
What should you do?
A.
You should create a DML INSTEAD OF trigger that will write the failed records to a file or table in order to accomplish this.
B.
You should drop the foreign key as your first step.
Then you can import the data by making use of the script.
Lastly you can re-create the foreign key.
C.
You should create a DML AFTER trigger that writes the failed records either to a file or table.
D.
You should create a CHECK constraint to ensure that the import process doesn’t fail.
Explanation:
A DML INSTEAD OF trigger causes SQL Server to carry out the code in the trigger instead of the operation that caused the trigger to fire. If this is used properly it will write a record missing the ProductCode to an alternative destination and not stop the import.