Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 151 discussion

Actual exam question from Microsoft's 70-762

Question #: 151
Topic #: 1

DRAG DROP -
You create tables by using the following Transact-SQL statements:

Each customer may have multiple addresses but only one is the primary address.
You must plan a solution that meets the following requirements:
✑ Return both customers and address information.
✑ Return only the primary address of the customer.
✑ Allow updates of all customer information and address details with the exception of the identity columns and the IsActive column.
Which three actions should you perform is sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

Step 1:
SQL Server Views WITH CHECK OPTION. Views can be created in SQL Server WITH CHECK OPTION. WITH CHECK OPTION will make sure that all INSERT and UPDATE statements executed against the view meet the restrictions in the WHERE clause, and that the modified data in the view remains visible after
INSERT and UPDATE statements.
Step 2-3:
INSTEAD OF triggers can be created on a view to make a view updatable.
References:
http://zarez.net/?p=3002
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

by othman_ee at Jan. 26, 2020, 7:50 a.m.

Comments

Submit Cancel

Anette

Highly Voted 4 years, 11 months ago

I think this is the answer. Do you agree? 1. Create a view joining both tables and showing all column with a where statement 2. Add the INSTEAD OF UPDATE statement to the view 3. Allow the application to query and update records directly from the view

upvoted 22 times

Lukis92

4 years, 11 months ago

You are not able to add INSTEAD OF UPDATE statement to the view. It's a trigger command.

upvoted 2 times

Cococo

4 years, 9 months ago

google CREATE VIEW

upvoted 2 times

...

lh2607

4 years, 7 months ago

INSTEAD OF UPDATE triggers can update Views. Please don't comment if you're not certain.

upvoted 3 times

...

othman_ee

Highly Voted 5 years, 5 months ago

3d section is wrong: can' t put a trigger on a table valued function! should've been view

upvoted 15 times

JohnFan

5 years, 4 months ago

You mean “Allow the application to query and update records directly from the view.”?

upvoted 14 times

...

gmu

Most Recent 4 years, 10 months ago

I think the view shows the IsActive = 1. If you use with check option you can't update the data because you can't show the result in the view.

upvoted 2 times

...

amar111

5 years, 1 month ago

shouldn't the fist box be - "Create a view joining both tables and showing all column with a where statement " . why is with check option required . its not mentioned anywhere in question that update should only be made on rows that appear in view result

upvoted 6 times

HPLovesCrafts

5 years ago

I agree. All the things that a Check Option would cover, can also be covered by the trigger, right? And since it seems like the trigger needs to be included anyway, the WITH CHECK option is superfluous...

upvoted 2 times

...

Matt95

5 years ago

"Return only the primary address of the customer"

upvoted 4 times

HA2020

4 years, 5 months ago

Matt95: 'Check option' controls what can be updated, not what the view returns. The where clause controls that.

upvoted 1 times

BabyBee

4 years, 5 months ago

The where clause can limit the data returned (what the user will see), but then the person making the update can update the IsActive field if the check option is not placed. As this is required by the question not to allow to update this field which the view will return as it will show both tables' data.

upvoted 2 times

...