Question 4) 2005 Implementation and Maintenance

Posted on
Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

Objective: Creating and Implementing Database Objects
SubObjective: Implement Triggers

Single Answer Multiple Choice

You are the database administrator for a major electronics chain. The company stores all the customer-related data in the Customers database that resides on the Sql1 server in the head office of the company. Customer-related data from other branches of the company is regularly sent to the head office and uploaded into the Customers database.

The data from the branch offices of the company is sent in the form of export files. You import the data by using the bcp utility and want to ensure that the insert triggers are not fired during the import process.

Which method will you use to most effectively achieve the objective?

A. Disable the triggers while performing the import process.
B. Drop the triggers before the import process, and then recreate them.
C. Use appropriate arguments while performing the import process to disable the firing of triggers.
D. Do nothing because the triggers will not be fired when you perform an import by using the bcp utility.

Answer:
D. Do nothing because the triggers will not be fired when you perform an import by using the bcp utility.

Tutorial:
The option stating that you should not do any anything is correct because triggers are not fired when you perform an import by using the bcp utility is correct. Triggers are disabled for a bulk import operation performed by using the bcp utility. Therefore, in this scenario, you are not required to do anything to disable the firing of triggers. If you want the triggers to be fired during the import process by using the bcp utility, you should use the h “FIRE_TRIGGERS” hint in the bcp command. Triggers are not fired when you import data by using the BULK INSERT statement. If you want triggers to be fired during the import process, you should use the FIRE_TRIGGERS argument in the BULK INSERT statement. If you perform an import by using the INSERT … SELECT * FROMOPENROWSET (BULK…) statement, triggers will be fired by default. If you want to disable the firing of triggers during the process, you should use the IGNORE_TRIGGERS hint.

The option stating that you should disable the triggers while performing the import process is incorrect because triggers are not fired when you perform an import by using the bcp utility.

The option stating that you should drop the triggers before the import process and then recreate them is incorrect because triggers are not fired when you perform an import by using the bcp utility. Therefore, you are not required to drop and recreate the triggers.

The option stating that you should use appropriate arguments while performing the import process to disable the triggers to be fired is incorrect because triggers are not fired when you perform an import by using the bcp utility. You can specify hints to enable the triggers to be fired.

Reference:
MSDN, Search, “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Database Engine,” “Administering the Database Engine,” “Importing and Exporting Bulk Data,” “Optimizing Bulk Import Performance,” “Controlling Trigger Execution When Bulk Importing Data.”

These questions are derived from the Self Test Software Practice Test for Microsoft exam 70-431-TS: Microsoft SQL Server 2005 Implementation and Maintenance

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: