community.borland.com

Article #25475: Inserting and Updating through a view - no triggers involved

Problem:
Can I insert and update through a view?

Solution:
create database "views.gdb" user "sysdba" password "masterkey";
commit;

set auto off;

create table base_table (
  key_value integer not null,
  flag_a integer,
  flag_b integer,
  flag_c integer
  );
commit;

grant all on base_table to public;
commit;

create unique index base_table_unq_idx on base_table(key_value);
commit;

create view base_table_view (
  view_key_value,
  view_flag_a,
  view_flag_b,
  view_flag_c) as
  select * from base_table;
commit;

grant all on base_table_view to public;
commit;

/* insert into the base table */
/* this will work */

insert into base_table (key_value, flag_a, flag_b, flag_c) values
  (1, 0, 0, 0);
commit;

select * from base_table;
commit;

/* insert into the table view */
/* this will work */

insert into base_table_view (view_key_value, view_flag_a, view_flag_b, view_flag_c) values
  (2, 0, 0, 0);
commit;

select * from base_table_view;
commit;

/* insert a dublicate value into the base table */
/* this will fail */

insert into base_table (key_value, flag_a, flag_b, flag_c) values
  (1, 0, 0, 0);
commit;

/* insert a dublicate value into the table view */
/* this will fail */

insert into base_table_view (view_key_value, view_flag_a, view_flag_b, view_flag_c) values
  (1, 0, 0, 0);
commit;

/* change values for the first record */
/* doing it on the base table */
/* this will work 	*/

update base_table set
  flag_a = 1,
  flag_b = 2, 
  flag_c = 3
where key_value = 1;
commit;

select * from base_table;
commit;

/* change values for the first record */
/* doing it on the base table */
/* this will work */

update base_table_view set
  view_flag_a = 3,
  view_flag_b = 2, 
  view_flag_c = 1
where view_key_value = 2;
commit;

select * from base_table_view;
commit;

/* reset the values via the base table */
/* this will work */

/* specify a range */

update base_table set
  flag_a = 0,
  flag_b = 0, 
  flag_c = 0
where key_value in (1,2);

select * from base_table;
rollback;

/* check that the rollback worked */

select * from base_table;

/* reset the values via the table view */
/* specify the whole table */
/* this will work */

update base_table set
  flag_a = 0,
  flag_b = 0, 
  flag_c = 0;
commit;

select * from base_table_view;
commit;

Last Modified: 26-OCT-00