Which Transact-SQL statement should you run?

You have a database that contains the following tables:

Customer

CustomerAudit

Where the value of the CustomerID column equals 3, you need to update the value of the CreditLimit column to 1000 for the customer. You must ensure that the change to the record in the Customer table is recorded on the CustomerAudit table.

Which Transact-SQL statement should you run?

A)

B)

C)

D)

A . Option A
B . Option B
C . Option C
D . Option D

View Answer

Answer: D

Explanation:

The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the tablevariable.

Incorrect Answers:

C: The deleted.Creditlimit should be inserted in the second column, the OldCreditLimit column, not the third column.

References: https://msdn.microsoft.com/en-us/library/ms177564.aspx