Solved

Lock records in oracle table based on username

Posted on 2013-02-06
11
557 Views
Last Modified: 2013-02-06
I need to create a trigger in Oracle 10g, in which a record in the table can be locked by any user, but unlocked by only one or two specific ones. I have a column TAG_LOCK and it is available to everyone to write to and now I want to create the trigger where if username is not the designated "un-locker" the record cannot be changed. Below is what I have so far, but it is not working for me - the record is still locked for everyone. Could some point out what I have wrong? Thanks!

create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END;
0
Comment
Question by:PopoDi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860574
You don't have the references.  I also simplified it a little.

Try this:

WHEN (upper(:OLD.TAG_LOCK)='Y' )
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860576
Make username upper case:

IF (sys_context('USERENV','SESSION_USER') <> 'POPVICD') THEN
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860593
Guess I should have tested it before posting...

It works for me:

drop table tab1 purge;
create table tab1(tag_lock char(1));


create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON tab1
FOR EACH ROW
WHEN (upper(OLD.TAG_LOCK) ='Y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END; 
/

show errors


insert into tab1 values('Y');
update tab1 set tag_lock='Z';



update tab1 set tag_lock='Z'
                     *
ERROR at line 1:
ORA-20202: THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to
your application.
ORA-06512: at "BUD.INSTR_LOCK", line 3
ORA-04088: error during execution of trigger 'BUD.INSTR_LOCK'

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860601
>>Make username upper case:

Good catch!  Missed that!
0
 

Author Comment

by:PopoDi
ID: 38860698
Thanks so much for the answers. I changed the trigger to use the upercase on the username (my username), but it is still not letting me change the record - I should be able to change it back to unlocked by changing the value in TAG_LOCK field.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860738
Make sure you're connected as POPVICD and while connected as POPVICD run this query to double check you're getting POPVICD:

select sys_context('USERENV','SESSION_USER') from dual;


Can you also post your update statement?
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860752
It works for me as well:

drop table instr purge
/
create table instr(TAG_LOCK char);
/
create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> USER) THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END;
/
insert into instr values('Y')
/
update instr set tag_lock = 'N'
/
update instr set tag_lock = 'Y'
/


table INSTR dropped.
table INSTR created.
TRIGGER INSTR_LOCK compiled
1 rows inserted.
1 rows updated.
1 rows updated.
0
 

Author Comment

by:PopoDi
ID: 38860804
I had to change it to OS_USER to get it to return my username but the trigger still would not let me unlock the record. I do not have an update statement - the unlock is done manually through a devex application or ms access (the screenshot of the error from within access included).


SYS_CONTEXT('USERENV','OS_USER')                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
popvicd
lockerror.bmp
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 38860861
Please change your trigger to this and show us the error:

create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (SYS_CONTEXT('USERENV','OS_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED BY '||SYS_CONTEXT('USERENV','OS_USER')||' Click OK to return to your application.');
END IF;
END;
/
0
 

Author Closing Comment

by:PopoDi
ID: 38860902
duh! Thank you so much for bearing with me, I should have added the username in the error message right from the start!
I needed to add the domain to the username as well and now it works.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860914
Good for you!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question