You have a view that includes an aggregate. You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses. You need to ensure that you can update the view. What should you create?
Suggested Answer:B🗳️
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql
Answer is wrong because schemabinding affects only schema of underlying tables. Views with aggregates can be changed via 'instead of triggers'. D is correct. See 'Updatable views': https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15
another very important method of making any view
modifiable using an INSTEAD OF TRIGGER object that will allow any view to be editable, even if the view doesn’t reference any TABLE objects in the database.
Agree that it's D
Because the view contains aggregates, a single row in the result set is potentially multiple rows from the base table(s). You will need an INSTEAD OF TRIGGER to make that type of view updatable.
If a view conforms to a certain set of rules, it's updateable without using an INSTEAD OF TRIGGER. Just remember that "generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table"
Like everyone is saying, D is the right answer. However, there is an alternative to create an updateable view without using an instead of trigger. There are a few restrictions on that type of view though, one is that it must use schema binding and another is it cannot have any aggregates. The existence of aggregates in this view is one reason why it isn't directly updateable.
I am afraid that databasejamdown is wrong regarding the restrictions: "There are a few restrictions on that type of view though, one is that it must use schema binding and another is it cannot have any aggregates."
In fact, aggregates are not a problem for updateable view. In addition to this, SCHEMABINDING is not required at all.
Here is an example to prove the point:
CREATE TABLE dbo.PARENT_TABLE
(
PARENT_TABLE_ID int NOT NULL,
COL_A varchar(128) NOT NULL,
CONSTRAINT PK_PARENT_TABLE PRIMARY KEY (PARENT_TABLE_ID)
)
GO
INSERT INTO dbo.PARENT_TABLE(PARENT_TABLE_ID, COL_A)
VALUES (1, 'Test 1');
GO
CREATE TABLE dbo.CHILD_TABLE
(
CHILD_TABLE_ID int NOT NULL,
PARENT_TABLE_ID int NOT NULL,
COL_B int NOT NULL,
CONSTRAINT PK_CHILD_TABLE PRIMARY KEY (CHILD_TABLE_ID),
CONSTRAINT FK_CHILD_TABLE__PARENT_TABLE FOREIGN KEY (PARENT_TABLE_ID)
REFERENCES dbo.PARENT_TABLE (PARENT_TABLE_ID)
)
GO
INSERT INTO dbo.CHILD_TABLE(CHILD_TABLE_ID, PARENT_TABLE_ID, COL_B)
VALUES (1, 1, 10), (2, 1, 20);
GO
CREATE VIEW dbo.My_VIEW
AS
SELECT P.*, (SELECT SUM(COL_B) FROM dbo.CHILD_TABLE AS C WHERE C.PARENT_TABLE_ID = P.PARENT_TABLE_ID) AS TOTALS
FROM dbo.PARENT_TABLE AS P
GO
INSERT INTO dbo.My_VIEW(PARENT_TABLE_ID, COL_A)
VALUES (2, 'Test 2');
UPDATE dbo.My_VIEW
SET COL_A = 'Test 3'
WHERE PARENT_TABLE_ID = 2;
As you can see, you can easily insert and update a record in the dbo.PARENT_TABLE table via dbo.My_VIEW.
The only reason why INSTEAD OF trigger is required in the question is that requirement: "The changes must be reflected in the tableS that the view uses."
You cannot insert to a view such a way that more than one table is affected.
The question states that the values of COLUMNS in the view is changed and that the changes must be reflected in the tables that the view. They're not referring to rows. I think B is the correct answer.
Wrong. Schemabinding has nothing common with values in columns. Schemabinging prevents Data Definition Changes on underlaying objects , not Data Manipulation Changes
SCHEMABINDING Protects the view from changes to the objects used in the
SELECT statement. For example, if you reference Table1.Column1, the properties of
that Column1 cannot be changed, nor can Table1 be dropped. Columns, not
references can be removed, or new columns added.
upvoted 3 times
...
This section is not available anymore. Please use the main Exam Page.70-762 Exam Questions
Log in to ExamTopics
Sign in:
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.
New_user
Highly Voted 5 years, 3 months agoJohnFan
5 years, 2 months agoHoglet
4 years, 10 months agostrikersree
Highly Voted 4 years, 10 months agodatabasejamdown
4 years, 7 months agoAlex5x
4 years, 5 months agoAlex5x
4 years, 5 months agoAlex5x
4 years, 5 months agoAnnelize
Most Recent 4 years, 3 months agoAndy7622
4 years, 3 months agoJohnFan
5 years, 2 months ago