Updating a View with CHECK

Question

Updating a View with CHECK

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
September 14, 2016 at 10:36 pm

#100937

Comments posted to this topic are about the item Updating a View with CHECK
September 14, 2016 at 10:37 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

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

Magnus Ahlkvist Ten Centuries Points: 1000 More actions · Answer 1

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.

Sql Server blog: http://www.tsql.nu

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 2

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.

The caveat here is that the row probably isn't returned from the view in the first place, since the employee with ID = 2 has a HireDate = 2001-02-26. At least in our version of AdventureWorks.

As posted, the question is impossible to answer since we don't know what hire date Employee 2 has in Steve's database.

And, yes. I guessed the same as you...:-P

Just because you're right doesn't mean everybody else is wrong.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 3

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.

+1

Reading this question, it seems logical to assume that there is a record with id 2.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 4

Mighty (9/15/2016)
+1
Reading this question, it seems logical to assume that there is a record with id 2.

And also assume that it's original value for the HireDate falls within the CHECK constraint. Which, in Steve's database, it probably doesn't.

Just because you're right doesn't mean everybody else is wrong.

Magnus Ahlkvist Ten Centuries Points: 1000 More actions · Answer 5

Yes clearly. Otherwise it will of course not throw an error, because there is no row that the query will attempt to update.

Sql Server blog: http://www.tsql.nu

matthias.elflein SSC Veteran Points: 241 More actions · Answer 6

Sorry - I disagree with the "correct" answer!

If you modify records that will violate the WHERE-condition, the UPDATE-query will terminate with error msg 550

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 7

funbi

SSCarpal Tunnel

Points: 4992

September 15, 2016 at 2:10 am

#1901037

Like everyone else I get an error.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

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

shaneoneillis SSCommitted Points: 1750 More actions · Answer 9

shaneoneillis

SSCommitted

Points: 1750

September 15, 2016 at 2:34 am

#1901042

Interesting how it fails with no error, thanks Steve!

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 10

shaneoneillis (9/15/2016)
Interesting how it fails with no error, thanks Steve!

Disregarding the fact that it doesn't, of course...:-P

Just because you're right doesn't mean everybody else is wrong.

shaneoneillis SSCommitted Points: 1750 More actions · Answer 11

Rune Bivrin (9/15/2016)
shaneoneillis (9/15/2016)
Interesting how it fails with no error, thanks Steve!
Disregarding the fact that it doesn't, of course...:-P

yeah...more coffee please...:pinch:

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

Yeah, I think the answer depends on what the hire date of Employee 2 in the database is, and since we don't know that (the question doesn't specify) then either one of two of the given answers could be correct. I happened to pick the wrong one of the two.

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 13

Interesting question and even more interesting answer, thanks Steve 🙂

The answer "The row is not updated and no error is returned."

should rather be listed as: "No row is not updated and no error is returned."

and would apply for this situation:

CREATE TABLE [dbo].[Employees](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[LastName] [nvarchar](20) NULL,

[HireDate] [datetime] NULL);

GO

INSERT [dbo].[Employees]

VALUES ('Steve', '20160202'), ('George', '20150228');

select * from [dbo].[Employees];

GO

CREATE VIEW NewEmployees2

AS

SELECT EmployeeID

, LastName

, HireDate

FROM dbo.Employees

WHERE HireDate > '20160101'

WITH CHECK OPTION;

GO

UPDATE dbo.NEwEmployees2

SET HireDate = '20150330'

WHERE EmployeeID = 2;

GO

DROP VIEW [dbo].[NewEmployees2];

DROP TABLE [dbo].[Employees];