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

Jeremy wrote:
> 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?
>

Such views are called symmetric views. They force that whatever goes in must come out.

Try:
  create or replace view mytab_v
  as
  (select *
   from mytab
   where filter_col = 'Y')
WITH CHECK OPTION; Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab