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
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
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.');