You are a database developer on an instance of SQL Server 2008. Your Prod database contains a WorkOrderDetail table that stores information about work being processed within your facility.
As rows are inserted into the WorkOrderDetail table, you want to gather and record information in the Status table, which is defined as follows:
If a row cannot be successfully added to the WorkOrderDetail table, three requirements must be met:
• A description of the error that occurred should be recorded in the ErrDesc column.
• The total number of work orders in progress should be recorded in the WOCount column.
• The date and time that an error occurred should be stored in the ErrDateTime column.
Which action should you take?
A. Enable Change Tracking for your Prod database.
B. Create a table-level CHECK constraint.
C. Create an INSTEAD OF INSERT trigger on the WorkOrderDetail table.
D. Create an AFTER INSERT, UPDATE trigger on the WorkOrderDetail table.
Correct Answer: C
Explanation/Reference:
An INSTEAD OF trigger fires instead of the operation that fired the trigger. INSTEAD OF triggers can be used to prevent the original operation from taking place or to perform additional or alternative actions. In this scenario, an INSTEAD OF INSERT trigger could include code to capture the additional information and insert it into the Status table.
You should not create an AFTER INSERT, UPDATE trigger on the WorkOrderDetail table. An AFTER trigger only fires after the triggering statement completes successfully. In this scenario, you needed to log errors that prevented rows from being inserted. This could not be performed in an AFTER trigger.
You should not create a table-level CHECK constraint. A CHECK constraint is used to restrict the data that is allowed for a column to specific values.
A CHECK constraint consists of a Boolean expression that evaluates to either TRUE or FALSE. If the expression evaluates to TRUE, the value is allowed for the column, and if the expression evaluates to FALSE, the value is not allowed for the column. CHECK constraints can be defined at the table level or column level, but only CHECK constraints defined at the table level can use columns other than the constrained column in the constraint expression.
You should not enable Change Tracking for your Prod database. The Change Tracking feature allows you to track rows in a table that were modified by DML statements. Detailed information about the data that was modified is not tracked, but the fact that rows in the table were changed is recorded in internal tables. This information can then be accessed using special change tracking functions.
