Home » SQL & PL/SQL » SQL & PL/SQL » WITH CHECK OPTION !!!!!
icon9.gif  WITH CHECK OPTION !!!!! [message #266106] Sun, 09 September 2007 14:59 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

when we create table like that :-

create table empl3 as select*from employees;


and then make this code :-

INSERT INTO  (SELECT employee_id, last_name, email,
                 hire_date, job_id, salary
              FROM   empl3 
              WHERE  department_id = 50                   WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
        TO_DATE('07-JUN-99', 'DD-MON-RR'), 
        'ST_CLERK', 5000);


why give this error :-

              FROM   empl3
                     *
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation


All what I want exactly that's what is the meaning of the WITH CHECK OPTION in the last code ?

[Edit (MC): remove font and size 4]

[Updated on: Mon, 10 September 2007 00:54] by Moderator

Report message to a moderator

Re: WITH CHECK OPTION !!!!! [message #266107 is a reply to message #266106] Sun, 09 September 2007 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>All what I want exactly that's what is the meaning of the WITH CHECK OPTION in the last code ?

Are you unwilling or incapable of using GOOGLE yourself?

Results 1 - 10 of about 1,790,000 for oracle sql WITH CHECK OPTION

The first document
http://dbis.ucdavis.edu/courses/sqltutorial/tutorial.pdf
on page 21 completely explain the SQL syntax
Re: WITH CHECK OPTION !!!!! [message #266125 is a reply to message #266106] Sun, 09 September 2007 22:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

ORA-01402:	view WITH CHECK OPTION where-clause violation
Cause:	An INSERT or UPDATE statement was attempted on a view 

created with the CHECK OPTION. This would have resulted in the 

creation of a row that would not satisfy the view's WHERE clause.

Action:	Examine the view's WHERE clause in the dictionary table 
VIEWS. If the current view does not have the CHECK OPTION, then 
its FROM clause must reference a second view that is defined 
using the CHECK OPTION. The second view's WHERE clause must also be 
satisfied by any INSERT or UPDATE statements. To insert the row, 
it may be necessary to insert it directly into the underlying table
, rather than through the view.

[Updated on: Sun, 09 September 2007 22:25]

Report message to a moderator

Re: WITH CHECK OPTION !!!!! [message #266126 is a reply to message #266106] Sun, 09 September 2007 22:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

For tables, WITH CHECK OPTION guarantees that inserts and updates result in tables that the defining table subquery can select. For views, WITH CHECK OPTION cannot make this guarantee if:



There is a subquery within the defining query of this view or any view on which this view is based or


INSERT, UPDATE, or DELETE operations are performed using INSTEAD OF triggers.

Re: WITH CHECK OPTION !!!!! [message #266143 is a reply to message #266106] Mon, 10 September 2007 00:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please stop using those big and bold fonts. It looks very aggressive.

Moved your question to newbie-forum. Questions about syntax of statements that can be found in the docs are not Expert questions.

[Updated on: Mon, 10 September 2007 00:41]

Report message to a moderator

Re: WITH CHECK OPTION !!!!! [message #266144 is a reply to message #266106] Mon, 10 September 2007 00:42 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
http://www.dba-oracle.com/t_with_check_option.htm
Previous Topic: how to compare two schemas and get all tables that are not in other schema
Next Topic: Trigger
Goto Forum:
  


Current Time: Sun Dec 04 10:40:21 CST 2016

Total time taken to generate the page: 0.16466 seconds