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