Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 3 discussion

Actual exam question from Microsoft's 70-762

Question #: 3
Topic #: 1

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:

Show Suggested Answer

Suggested Answer:

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.
References:
https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

by Cageman at Jan. 9, 2020, 5:25 p.m.

Comments

Submit Cancel

Twigit

Highly Voted 5 years, 4 months ago

Provide Custom - INSTEAD OF INSERT because we should provide a custom error instead of inserting the value Update Customer Table - AFTER INSERT because we should Update the Customer table after we insert a record into the Order table

upvoted 43 times

...

alisher

Highly Voted 5 years, 5 months ago

Update the value – After insert. Because creating new order is INSERT tran, not UPDATE.

upvoted 30 times

Bartek

5 years, 5 months ago

agree:)

upvoted 5 times

...

Anette

5 years ago

agree :)

upvoted 3 times

...

kimalto452

4 years, 6 months ago

agree :)

upvoted 1 times

...

jeja

Most Recent 4 years, 5 months ago

whats the final correct answer?

upvoted 1 times

...

vrab

4 years, 5 months ago

For provide custom if you throw an error with rollback tran you can use after insert trigger, check msdn, I think is after insert the answer for both.

upvoted 1 times

...

BrianILyas

4 years, 6 months ago

Backy is right, answer is INSTEAD OF INSERT for both custom error and update customer. AFTER INSERT is irrelevant when has INSTEAD OF INSERT.

upvoted 1 times

...

Alex5x

4 years, 7 months ago

Looking at the Orders table on the diagram, I can't see CustomerId column. This seems weird because an order is not linked to any customer. This means that you can easily insert a new record to Orders table for a customer that does not exist. In theory, we should have a foreign key (FK) which references Customers table, should't we?

upvoted 1 times

Alex5x

4 years, 7 months ago

If we don't have CustomerId column in the Orders table, then no matter which trigger - INSTEAD or AFTER - we will use. In both triggers we simply don't know CustomerId and, as a result: we cannot check the existanse of the customer we cannot update Customers table to set the value for the last order.

upvoted 1 times

...

Alex5x

4 years, 7 months ago

Let's assume that the diagram is wrong and Orders table has a CustomerId column. Now we can decide about the type of the trigger: If CustomerId is the FK, then you can only use INSTEAD OF trigger to return a custom error, otherwise you will get a standard FK violation error message. If CustomerId is not FK, then you can use AFTER INSERT trigger. CREATE TRIGGER dbo.TR1 ON dbo.Orders AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @CustomerId int; SELECT @CustomerId = CustomerId FROM INSERTED;--asssume only one record at a time is inserted. IF NOT EXISTS(SELECT 1 FROM Customers WHERE CustomerId=@CustomerId) BEGIN ROLLBACK TRAN; ;THROW 50000, 'This is a user-defined error.', 1; END UPDATE Customers SET LastOrder = ... WHERE CustomerId = @CustomerId; END So, the answer really depends on the missing data. If CustomerId is the FK, then INSTEAD OF INSERT trigger - for custom error and AFTER INSERT - for update Customers table If CustomerId is not FK, then AFTER INSERT trigger - for custom error and AFTER INSERT - for update Customers table

upvoted 2 times

...

Backy

4 years, 7 months ago

The question does not address the issue of linking order to a customer, but the most important issue here is that you cannot update table Customers if "a customer that does not exist", meaning all the triggers for this table are irrelevant

upvoted 1 times

...

Backy

4 years, 9 months ago

It should be INSTEAD OF INSERT for each column, the same trigger on the same table Orders. Here, you are supposed to update table Customer only when a new order is created, hence you should use INSTEAD OF INSERT on table Orders and not Customer, the same trigger can handle both cases

upvoted 1 times

...

strikersree

5 years ago

AFTER INSERT - Update Customer Table INSTEAD OF INSERT - Provide Custom Error Message

upvoted 3 times

stm22

4 years, 11 months ago

yes 'after inserting' Order for existing customer, Update Customer Table 'instead of inserting' Order for non-existing cust, provide error msg

upvoted 1 times

...

Nelly100

5 years, 3 months ago

The correct answer here Is INSTEAD OF INSERT because the user has attempted to insert a value and eventually fails meaning that nothing was inserted at all so their answer is wrong, it cant AFTER INSERT because nothing was inserted

upvoted 2 times

...

Cageman

5 years, 5 months ago

The answer is in the explanation: - INSTEAD OF INSERT (Provide) - AFTER update (Update)

upvoted 23 times

...