Re: Dont allow updation for few columns in a record.

HK wrote:

>hi all,
>thanx bruno.
>If we dont grant update access to the table, i will not be able to do any
>updations.
>I only want to prevent a particular column from being updated.
>Is there any way??
>TIA.
>
Split your table into two - one, containing the columns you wnat to
update, and the other one 'read-only', and make them related via a
foreign key:

create table prod_def
(
    id int primary key,
    name text
);
revoke update on prod_def from public;
create table prod_desc
(
    id int primary key references prod_def,
    description text
);
grant update on prod_def to public;

You can also set up a view, and a couple of rules, to make it look like
your original single table:

create view product as select def.id as prod_id, name as prod_name,
description as prod_desc from prod_def def natural join prod_desc;
create rule new_product as on insert to product do instead
(
   insert into prod_def values (new.prod_id, new.prod_name);
   insert into prod_desc values (new.prod_id, new.prod_desc);
);

create rule update_product as on update to product do instead
(
   update prod_desc set description = new.prod_desc, id = new.prod_id
where id = old.prod_id;
);

This will simply ignore attempts to change the product's name... If you
want such attempts to cause an error, you can
add this to the action:

update prod_def set name = new.prod_name where new.prod_name <>
old.prod_name and id = new.prod_id;

This will cause an exception if the user is trying to change the
product's name, and is not allowed to do that.

I hope, it helps...

Dima




>
>On Wed, 9 Jul 2003, Bruno Wolff III wrote:
>
>
>
>>On Wed, Jul 09, 2003 at 11:40:24 +0530,
>>  HK <harikrishnan@midascomm.com> wrote:
>>
>>
>>>Hi all,
>>>    Is it possible to say that once a record is inserted into the
>>>table, nobody can update some particular columns in that record.
>>>
>>>(eg)
>>>table
>>>------
>>>prod_id prod_name prod_desc
>>>  1      name1      desc1
>>>  2      name2      desc2
>>>
>>>In this table i dont want to allow updation of the prod_name column.
>>>
>>>I can write a trigger to do this job, but is there any way to specify
>>>during the table creation time itself that these columns cannot be
>>>altered.
>>>
>>>
>>One option would be not to grant UPDATE access to the table.
>>
>>
>>
>
>
>