VIEWS 2

Question

VIEWS 2

Viewing 13 posts - 16 through 27 (of 27 total)

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

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 1

Hugo Kornelis (3/12/2012)
...
And if the view includes the WITH CHECK option, those people would also be unable to change active members to non-active members.

I am not sure to understand perfectly the WITH CHECK option. From microsoft, I found that "WITH CHECK option" is:

"Forces all data modification statements that are executed against the view to follow the criteria set within select_statement."

So, If I understand well, all column from the view are protected against updates, but you can delete any row from the view (if it does not have reference constraint) or insert a new row ( if the view covers all "not null" columns). Is that it? That could be neet...

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

tilew-948340 (3/12/2012)
I am not sure to understand perfectly the WITH CHECK option.

Put simply, WITH CHECK means that rows that are inserted or updated through the view must still satisfy the view's predicate. In other words, if you do an insert or update through the view and then a select immediately after it, you should see the updated or inserted row.

Example:

CREATE VIEW SalesPpl

AS

SELECT EmpNo, Name, Salary, Department

FROM Personnel

WHERE Department IN ('Sales', 'SalesBackOffice');

The view above allows people to add personnel into the Marketing department, or to update a row to transfer someone from Sales to Accounting. The WITH CHECK option prevents that, while still allowing users to update a row to transfer people between Sales and SalesBackOffice.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Got any more like this Ron?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

SQLRNNR (3/13/2012)
Got any more like this Ron?

ONe more called VIEW 3 scheduled for the 20th of March and 2 more pending have been submitted .. waiting to be scheduled.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 5

Hugo Kornelis (3/13/2012)
...
The view above allows people to add personnel into the Marketing department, or to update a row to transfer someone from Sales to Accounting. The WITH CHECK option prevents that, while still allowing users to update a row to transfer people between Sales and SalesBackOffice.

oh... it was not what I tought...good thing I ask 😀

So much to learn... so much to understand...so much to remembered...

good thing you are there!

Thanks Hugo for the precision!

Thanks bitbucket for the question!

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

March 14, 2012 at 12:31 pm

#1459438

tks Ron

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 7

Good question. Thanks for submitting.

http://brittcluff.blogspot.com/

unlu.mehmet SSC Enthusiast Points: 103 More actions · Answer 8

unlu.mehmet

SSC Enthusiast

Points: 103

March 16, 2012 at 2:54 am

#1460366

:hehe:

unlu.mehmet SSC Enthusiast Points: 103 More actions · Answer 9

I think the question is a bit obvious

Why?

1) It is a view not a table. If you want to delete any columns, you can do it by excluding from view

2) It is a view so use view as a view not as a table

BudaCli Hall of Fame Points: 3404 More actions · Answer 10

wasn't obvious for me. never thought that it is possible to delete data in a view

What you don't know won't hurt you but what you know will make you plan to know better

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

very good question...

unfortunately I checked wrong answer 🙁

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 12

Dineshbabu

Hall of Fame

Points: 3220

January 10, 2013 at 6:34 am

#1575748

Nice One..

--
Dineshbabu
Desire to learn new things..

Hany Helmy SSChampion Points: 13921 More actions · Answer 13

Very good question, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant