VIEWS 2

  • 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...

  • 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/

  • 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

  • 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]

  • 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!

  • tks Ron

  • Good question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • :hehe:

  • 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

  • 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
  • very good question...

    unfortunately I checked wrong answer 🙁

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

  • Nice One..

    --
    Dineshbabu
    Desire to learn new things..

  • Very good question, thanx.

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

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