Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 125 discussion

Actual exam question from Microsoft's 70-762

Question #: 125
Topic #: 1

You run the following Transact-SQL statements:

Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created.
You need to ensure that you can insert rows into the Orders table by using the view.

A. Add the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.
B. Create an INSTEAD OF trigger on the view.
C. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.
D. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

Show Suggested Answer

Suggested Answer: A 🗳️
The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the
SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view.
References:
http://www.informit.com/articles/article.aspx?p=130855&seqNum=4

by StereoInvestigation at Feb. 16, 2020, 12:34 p.m.

Comments

Submit Cancel

moehijawe

Highly Voted 5 years, 5 months ago

I would go with answer B

upvoted 18 times

JohnFan

5 years, 4 months ago

Add an INSTEAD OF INSERT TRIGGER to the table. In the TRIGGER, we get the inserted and deleted virtual tables that are the shape of the VIEW objects structure, which we will use to do the INSERT operations

upvoted 2 times

...

StereoInvestigation

Highly Voted 5 years, 5 months ago

As there is no where clause in the view, wouldn't B make more sense?

upvoted 17 times

Kuratko

5 years, 4 months ago

If a customer name does not exist, then a new customer name must be created. -> B seems to be right.

upvoted 16 times

Sum658

5 years, 3 months ago

Yes, I will go with Kuratko. Even if we add CustomerID we cannot insert in the table because custumerID is defined as Identity. (If we assume all the names are unique then B is the correct answer.)

upvoted 1 times

...

RYP

5 years, 4 months ago

No, because there is a FK constraint on Orders.CustomerID, and the view performs an inner join on the Customers table, and finally the Customers table has a NOT NULL contraint on customer name. A WITH CHECK constraint ensures that you can't insert data using the view that the view cannot return, so if the customer name doesn't exist you won't be able to add via this view.

upvoted 2 times

JohnFan

5 years, 4 months ago

WITH CHECK OPTION checks to make sure that the result of the INSERT or UPDATE statement is still visible to the user of the view.

upvoted 1 times

...

Hoglet

5 years ago

"If a customer name does not exist, then a new customer name must be created" INSTEAD OF TRIGGER to create a Customer record if required.

upvoted 3 times

...

ZSQL

Most Recent 4 years, 8 months ago

''Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created. You need to ensure that you can insert rows into the Orders table by using the view.'' Can some one pls tell me ? if we use the view on the question you can't insert the any row if view got count function also you can't use count function, view has to be count_big() with schemabinding option

upvoted 1 times

...

KosteK

4 years, 9 months ago

Why not answer D?

upvoted 4 times

databasejamdown

4 years, 8 months ago

The subquery and the lack of schemabinding won't stop you from using a trigger to insert data using an instead of trigger. So it is unnecessary.

upvoted 2 times

...

mrn0107

5 years ago

Book - 168 page - The answer is B - Insted of trigger

upvoted 1 times

...

amar111

5 years, 1 month ago

I will go with "Instead off trigger" insert to view can only happen using "Instead off trigger" . all other requirements in question come after this .

upvoted 1 times

...

Nelly100

5 years, 3 months ago

Lets not mislead people please. The correct answer includes with Check Option. The requirement is to ensure that you can insert rows into the Orders table by using the view AND This can only be done using WITH CHECK OPTION! Period!!! Read more about it and get back to me

upvoted 1 times

Nickname17

5 years, 2 months ago

WITH CHECK OPTION checks to make sure that the result of the INSERT or UPDATE statement is still visible to the user of the view.

upvoted 1 times

Hoglet

5 years ago

Except the requirement is "If a customer name does not exist, then a new customer name must be created.". INSTEAD OF TRIGGER to create a Customer record if required.

upvoted 2 times

...

hgi

5 years, 2 months ago

surely this can be done using INSTEAD OF INSERT TRIGGER. Please do not mislead people!

upvoted 4 times

...

Mosufe

4 years, 10 months ago

First of all, you dont even need the WITH CHECK option to insert into one view. This just CHECK if the inserted value will be returned, but the question asks you to do more than that.

upvoted 1 times

...

Hoglet

5 years ago

Except the requirement is "If a customer name does not exist, then a new customer name must be created". That requires that the INSERT against the view also create a Customer record if required. INSTEAD OF TRIGGER to create a Customer record if required.

upvoted 2 times

...

Load full discussion...

...