Solved

Lock records in oracle table based on username

Posted on 2013-02-06
11
551 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
  • 5
  • 3
  • 3
11 Comments
 
LVL 76

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 76

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
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now