Re: Inserting through a view - forcing certain values into columns

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 12 Mar 2007 12:58:51 +0100
Message-ID: <45F5407B.2080505@arcor.de>

Jeremy schrieb:
> Hi
>
> Say you have a table:
>
> create table mytab
> (id number,
> name varchar2(30),
> filter_col varchar2(1));
>
> Then you create a view on this
>
> create or replace view mytab_v
> as
> (select *
> from mytab
> where filter_col = 'Y');
>
> insert into mytab_v (id, name, filter_col)
> values (1, 'xxx','N');
>
>
> I was wondering whether, on inserting a row via the view, the filter_col
> would automatically be given the value 'Y' or that if another value was
> supplied that some kind of implicit constraint might be violated. This
> doesn't appear to be the case, but is it possible to implement a view in
> such a way that such validation would be performed?
>

SQL> create table mytab

   2  (id            number,
   3           name          varchar2(30),
   4           filter_col    varchar2(1));

Table created.

SQL>
SQL> create or replace view mytab_v

   2 as
   3 (select *

   4           from    mytab
   5           where   filter_col = 'Y')

   6 with check option;

View created.

SQL>
SQL> insert into mytab_v (id, name, filter_col)

   2 values (1, 'xxx','N'); insert into mytab_v (id, name, filter_col)

             *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Best regards

Maxim Received on Mon Mar 12 2007 - 06:58:51 CDT