Updating a table through view

Database
Enthusiast

Updating a table through view

Hi,

It might be the basic question but wanted the reason from TD experts.

When i create a simple view from a single table, and update one of the row in that view, then changes will be reflected in its table. Please let me know what actually happens in back ground since if we see the defination of a view, its just a virtual table. how it can update row in its table.?

Regards,

Mahesh

4 REPLIES
Senior Apprentice

Re: Updating a table through view

Hi Mahesh,

it's the same a SELECTing from this view, the souce code is resolved by the parser and you actually access the base table:

When you EXPLAIN the update you'll notice that the base table is updated.

Dieter

Enthusiast

Re: Updating a table through view

Thank you Dieter. But i have few queries,

I created a table as below,  (V2R5)              

CREATE SET TABLE zam_product1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL,                     CHECKSUM = DEFAULT                    

(product_id INTEGER,                     

product_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,                   

sale_date DATE FORMAT 'dd-mm-yyyy',                      daily_sales DECIMAL(18,6))     

 PRIMARY INDEX ( product_id );

I created a view as below,            

 replace  view  zam_product_V2                as select               

product_name               

,sale_date              

  from jedi_mvn_db.zam_product1;

I granted update rights to user "jedi_cdw_dba" on column "product_name" as below, 

 grant select,update (product_name) on zam_product_v2  to jedi_cdw_dba;

then i see the rights given from dbc.allrights as below,

select * from dbc.allrights where tablename='zam_product_v2';

But in result i can see that update rights are given on column "sale_date"(2 nd column in view & 3rd column in table).

And when i run below query, it says sale_date column does not exist but it does exist in my view.

grant select,update (sale_date) on jedi_mvn_db.zam_product_v2to jedi_cdw_dba;

How view and its table is related to each other now?  Please explain me the reason, since i am very much confused because of this.

Mahesh

Senior Apprentice

Re: Updating a table through view

Hi Mahesh,

i don't know what caused this, are you 100% shure the create view/grant/select all refernce the correct objects in the correct database?

When the base table of a view is dropped the view will stop working ("table does not exist") but when a new table with the same name is created it will try to access this new table instead. Maybe you dropped/recreated the base table?

Btw, V2R5 is several years old, if this is a TD Express you should definitely use a current release.

Dieter 

Enthusiast

Re: Updating a table through view

Thank you Dieter. Will mark your words and check it accordingly.

Regards,

Mahesh