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

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.


TIA
--
regards,
hari
      __
     / /    __  _  _  _  _ __  __         -o)
    / /__  / / / \\// //_// \\ \\/ /         /\\\\  Making things happen
   /____/ /_/ /_/\\/ /___/  /_/\\_\\        _\\_v-

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.

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.

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.
>

--
regards,
hari
      __
     / /    __  _  _  _  _ __  __         -o)
    / /__  / / / \\// //_// \\ \\/ /         /\\\\  Making things happen
   /____/ /_/ /_/\\/ /___/  /_/\\_\\        _\\_v-

-------------------------------------------------------------------

On Fri, Jul 11, 2003 at 10:43:38 +0530,
  HK <harikrishnan@midascomm.com> 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??

Then things are harder. One way to do this is to use the rule system
to make an updatable view (that won't let you change the one column).
You can also probably do it with before triggers.

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.
>>
>>
>>
>
>
>

Has anyone considered implementing per-column updating?  Oracle has
this.
Given a table with columns a, b and c, you can do "grant update on
table(a,b) to user".

Jeff
On Friday, July 11, 2003, at 05:35  PM, Dmitry Tkach wrote:

> 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.
>>>
>>>
>>
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings