HOTSPOT
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named Sales that contains the following database tables: Customer, Order, and
Products. The Products table and the Order table are shown in the following diagram.
The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records.
Storage requirements for the Leads table must be minimized.
You need to create triggers that meet the following requirements:
Optimize the performance and data integrity of the tables.
Provide a custom error if a user attempts to create an order for a customer that does not exist.
In the Customers table, update the value for the last order placed.
Complete all actions as part of the original transaction.
In the table below, identify the trigger types that meet the requirements.
NOTE: Make only selection in each column. Each correct selection is worth one point.
Hot Area:
Answer: 
Explanation:
INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT
statement.
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement
have executed successfully.
https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx
Two new questions:
QUESTION
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
– Launch when table data is modified.
– Evaluate the state a table before and after a data modification and take action based on the difference.
– Prevent malicious or incorrect table data operations.
– Prevent changes that violate referential integrity by cancelling the attempted data modification.
– Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?
A. extended procedure
B. CLR procedure
C. user-defined procedure
D. DML trigger
E. scalar-valued function
F. table-valued function
Answer: D
QUESTION
You are developing and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SOL statements.
You need to create a database object.
What should you create?
A. extended procedure
B. CLR procedure
C. user-defined procedure
D. DML trigger
E. scalar-valued function
F. table-valued function
Answer: E
More: https://www.braindump2go.com/70-762.html
1
5
Thankyou..do you have a few more latest questions? I’ll have to take the exam this week..
1
0
And how was your exam? Have you passed it?
How many new questions?
0
0
Thanks
0
1
IMO the answer is wrong.
Provide custom error – Instead of insert.
AFTER INSERT usage is possible too, but it is important to prevent “wrong” transactions and subsequent rollbacks to optimize performance. Performance optimization is one of the requirements in this question.
Update the value – After insert.
Because creating new order is INSERT tran, not UPDATE.
43
0
But the question also states to “Complete all actions as part of the original transaction.” The instead of trigger replaces the insert action with the trigger logic.
By that, we would have to do an after insert logic to handle both would we not?
0
0
I take this back. You are correct. “The instead of trigger will execute in place of the actual insert… the code within the DML trigger is executed instead, as part of the triggering transaction.”
For the after insert “Original transaction will not commit or roll back until the trigger has also committed or [is] rolled back.”
From: https://books.google.com/books?id=Ua1CAwAAQBAJ&pg=PT386&lpg=PT386&dq=does+after+trigger+complete+actions+as+part+of+original+transaction&source=bl&ots=LIu-3TPN6b&sig=ACfU3U07iFwHon8-UWh6AvwdekK95eoVsA&hl=en&sa=X&ved=2ahUKEwjLyYfHoo7oAhXRLs0KHT-hDJIQ6AEwDnoECAkQAQ#v=onepage&q=does%20after%20trigger%20complete%20actions%20as%20part%20of%20original%20transaction&f=false
0
0