Re: Trigger to Prevent Update of Certain Columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 08 Feb 2000 16:36:03 -0500
Message-ID: <1v21as40tdc3eqh7k0m8d19bs6csth7e6f@4ax.com>

A copy of this was sent to jrcampbell_at_my-deja.com (if that email address didn't require changing) On Mon, 07 Feb 2000 16:01:26 GMT, you wrote:

>I am trying to prevent users from having the ability to update certain
>columns in a table, such as the UNIQUEID. My thought is to build a
>trigger that would replace the :new.UNIQUEID with the :old.UNIQUEID,
>etc....
>
>When I attempt to create the trigger I receive: "ORA-04082: NEW or OLD
>references not allowed in table level triggers"
>
>Any ideas?
>
>Here's my hack at it:
>
>accept officename prompt "Enter OfficeName In UPPERCASE: "
>
>spool create_&officename._divdata_trigger
>
>--*
>--create/replace the trigger
>--*
>create or replace trigger
> trg_&officename._divdata
>before
> update
>on
> &officename._divdata
>BEGIN
> if updating then
> :new.mapoffice := :old.mapoffice,
> :new.dmap := :old.dmap,
> :new.uniqueid := :old.uniqueid,
> :new.x := :old.x,
> :new.y := :old.y;
> end if;
>END;
>/
>
>spool off
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

You could use a trigger for this -- it would be

create or replace trigger
....
before update on T

FOR EACH ROW                    <<<<----- you were missing this

begin

   ....
end;

I would probably be tempted to use a grant to prevent people from updating columns they should not:

ops$tkyte_at_8i> create table t ( x int, y int, z int ); Table created.

ops$tkyte_at_8i> insert into t values ( 1, 1, 1 ); 1 row created.

ops$tkyte_at_8i> grant select, update (x,y) on t to scott; Grant succeeded.

ops$tkyte_at_8i> connect scott/tiger
Connected.

scott_at_8i> update ops$tkyte.t set x = 1; 1 row updated.

scott_at_8i> update ops$tkyte.t set y = 1; 1 row updated.

scott_at_8i> update ops$tkyte.t set z = 1; update ops$tkyte.t set z = 1

                 *

ERROR at line 1:
ORA-01031: insufficient privileges

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 08 2000 - 15:36:03 CST