Exam 70-761 topic 1 question 123 discussion

Actual exam question from Microsoft's 70-761

Question #: 123
Topic #: 1

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. Option A
B. Option B
C. Option C
D. Option D

Show Suggested Answer

Suggested Answer: A 🗳️
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:
D: 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

by Dieter at Aug. 19, 2019, 4:21 p.m.

Comments

Submit Cancel

Dieter

Highly Voted 5 years, 10 months ago

No, I think the correct answer is B since the OUTPUT parameters do not fit the insert into parameters. in addition, the inserted values are incorrect (deleted for both old and new credit limit)

upvoted 42 times

...

okh

Highly Voted 5 years, 9 months ago

B is correct

upvoted 8 times

...

aditi66

Most Recent 4 years, 5 months ago

d is correct

upvoted 1 times

...

Korongo

4 years, 5 months ago

Most correct is B.

upvoted 1 times

...

RajinderKaur

4 years, 6 months ago

I did the exam today and this question was on there. However there was an option where the parameter for OldCreditLimit was deleted.CreditLimit and the parameter for NewCreditLimit was inserted.CreditLimit . I had to read the answers carefully as there was another option where both these parameters were deleted.CreditLimit

upvoted 1 times

...

chaoxes

4 years, 11 months ago

B. is correct. A Can't be correct because it wants to INSERT deleted.CreditLimit (old credit limit) into NewCreditLimit column, which is false. Also A will produce error since there is no value defined for "ChangedBy" column

upvoted 1 times

...

stm22

4 years, 11 months ago

B is correct

upvoted 1 times

...

Marshy

5 years, 2 months ago

The answer is B : values for oldCreditLimit & newCreditLimit must come form correct inserted/ deleted values and also the changedBy value must be supplied... whihc it isn't in A

upvoted 1 times

...