Home » SQL & PL/SQL » SQL & PL/SQL » Allow updating on few columns (3 threads merged by bb)
Allow updating on few columns (3 threads merged by bb) [message #382829] Sun, 25 January 2009 10:07 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
Hi,

On table say xyz has 100 columns. A before update row level trigger should allow updation on few columns(say only 3 columns are allowed to update). If user try to update columns other than the specified 3 columns then raise error.

This can be done using

if :old.col1 <> :new.col1 or :old.col2 <> :new.col2 or :old.col3 <> :new.col3 ..... upto 97 columns
then
raise updation_not_allowed.

This is too lengthy and seems to be a bad coding.

Is there any other way this can be done with few lines of code.

Appreciate for your help in advance.

Regards
Re: Triggers - Allow updating on few columns [message #382832 is a reply to message #382829] Sun, 25 January 2009 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Is there any other way this can be done with few lines of code.
NO code is needed.
Manually inspect SQL before deploying code into production to only allow the 3 desired columns can be changed.
Re: Triggers - Allow updating on few columns [message #382833 is a reply to message #382829] Sun, 25 January 2009 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do NOT multi-post
Re: Triggers - Allow updating on few columns [message #382834 is a reply to message #382829] Sun, 25 January 2009 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do NOT multi-post
Re: Allow updating on few columns (3 threads merged by bb) [message #382835 is a reply to message #382829] Sun, 25 January 2009 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not allow the user to update the other columns, that is only grant privilege to update the 3 allowed ones.

Regards
Michel
Re: Allow updating on few columns (3 threads merged by bb) [message #382836 is a reply to message #382835] Sun, 25 January 2009 12:34 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
example syntax for granting update on only three columns:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9013.htm#i2126048
Previous Topic: Writing Quiry (merged)
Next Topic: INDEX
Goto Forum:
  


Current Time: Fri Dec 09 02:18:58 CST 2016

Total time taken to generate the page: 0.15681 seconds