Updating a View with CHECK

Question

Updating a View with CHECK

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

Magnus Ahlkvist (9/15/2016)
I don't Think the correct answer is correct.
When I try to update a view (defined using WITH CHECK OPTION) with a value which makes the row fall outside of, I get an error message:
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

That's what I got.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

When I tried it, it did not update and I did get an error. This was on 2008R2. So either they quit providing the error on newer versions of sql or the stated answer is wrong.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

Carlo Romagnano (9/15/2016)
The correct answer is "Depends", if the view returns or not a row:
No row returned: -> The row is not updated and no error is returned.
Row returned: -> No update occurs, and an error is returned that notes this update violates the view definition

Well stated, thanks.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

This sure is one to get us thinking, isn't it? 😛

I got the right answer, but given the debate, I had to create the scenario. If there are no employees meeting the date criteria of the view, it doesn't update a row and doesn't throw an error. If there are employees meeting the date criteria, it does throw an errors.

A good question to be sure. Thanks, Steve.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

j-1064772 SSCertifiable Points: 5316 More actions · Answer 5

I definitely have to bone up on the WITH CHECK option.

Thank you for bringing this up Steve.

Learn something new every day.

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Carlo Romagnano (9/15/2016)
The correct answer is "Depends", if the view returns or not a row:
No row returned: -> The row is not updated and no error is returned.
Row returned: -> No update occurs, and an error is returned that notes this update violates the view definition

Yes, QotD should state what is the hire date before the attempted update.

Nonetheless, thanks to Steve for the question.

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 7

Sorry, I was testing this and should have included. I had a hiredate of pre 2016 already in there. Should have shown the initial data.

Corrected the question, awarding back points.

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 8

Steve Jones - SSC Editor (9/15/2016)
Sorry, I was testing this and should have included. I had a hiredate of pre 2016 already in there. Should have shown the initial data.
Corrected the question, awarding back points.

Thanks Steve, I just got worried because I do not normally update views and so I had to learn something here. I found a post on social.msdn forums and they stated:

CREATE VIEW authors_CA AS
(
SELECT * FROM Authors WHERE state='CA'
)
WITH CHECK OPTION
Now any insert or update operation that makes a record disappear from the view raises a trappable runtime error.

Because nothing was said about the data I accepted that the date was the only thing out of the ordinary. Thanks again for the opportunity to learn something new. :cool::cool::cool:

Manie Verster
Developer
Johannesburg
South Africa

I am happy because I choose to be happy.
I just love my job!!!

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Steve Jones - SSC Editor (9/15/2016)
Sorry, I was testing this and should have included. I had a hiredate of pre 2016 already in there. Should have shown the initial data.
Corrected the question, awarding back points.

Unfortunately that couldn't correct the question in the 15th Sept newsletter.

Tom

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP