ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 125 discussion

Actual exam question from Microsoft's 70-762
Question #: 125
Topic #: 1
[All 70-762 Questions]

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 Hide 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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
moehijawe
Highly Voted 5 years, 2 months ago
I would go with answer B
upvoted 18 times
JohnFan
5 years, 1 month 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, 2 months ago
As there is no where clause in the view, wouldn't B make more sense?
upvoted 17 times
Kuratko
5 years, 2 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, 1 month 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, 2 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, 1 month 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
4 years, 10 months 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, 5 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, 6 months ago
Why not answer D?
upvoted 4 times
databasejamdown
4 years, 6 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
4 years, 10 months ago
Book - 168 page - The answer is B - Insted of trigger
upvoted 1 times
...
amar111
4 years, 11 months 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, 1 month 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 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
4 years, 10 months 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 ago
surely this can be done using INSTEAD OF INSERT TRIGGER. Please do not mislead people!
upvoted 4 times
...
Mosufe
4 years, 8 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
4 years, 10 months 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...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago