• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Lock records in oracle table based on username

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
PopoDi
Asked:
PopoDi
  • 5
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
You don't have the references.  I also simplified it a little.

Try this:

WHEN (upper(:OLD.TAG_LOCK)='Y' )
0
 
paquicubaCommented:
Make username upper case:

IF (sys_context('USERENV','SESSION_USER') <> 'POPVICD') THEN
0
 
slightwv (䄆 Netminder) Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
>>Make username upper case:

Good catch!  Missed that!
0
 
PopoDiAuthor Commented:
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
 
paquicubaCommented:
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
 
paquicubaCommented:
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
 
PopoDiAuthor Commented:
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
 
paquicubaCommented:
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
 
PopoDiAuthor Commented:
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
 
paquicubaCommented:
Good for you!!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now