This discussion is archived
1 Reply Latest reply: Dec 9, 2009 12:30 PM by Andreas Weiden RSS

[Block based on From Query] On-Lock Trigger

JeanParis Newbie
Currently Being Moderated
Hi,

I have a block based on an Emp_Dept view.
I manage updating and inserting with the On-Update and On-Insert triggers.

I wonder if I have to code this in the On-Lock trigger:

message( 'Oracle locks' );
lock table emp in share row exclusive mode;

If I want Oracle to prevent another user from updating the same row I'm updating.

Thanks for your help.

JBM
  • 1. Re: [Block based on From Query] On-Lock Trigger
    Andreas Weiden Guru
    Currently Being Moderated
    This is the code we use to lock specific records in ON-LOCK:
    CURSOR crLock IS
      RESOURCE_BUSY EXCEPTION;
      PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -54);
     
      SELECT ID
        FROM THE_BASE_TABLE_OF_THE_VIEW
       WHERE ID=:BLOCK.ID FOR UPDATE OF ID NOWAIT;
      nDummy NUMBER;
      
    BEGIN
      OPEN crLock;
      FETCH crLock INTO nDummy;
      CLOSE crLock;
    EXCEPTION
      WHEN RESOURCE_BUSY THEN
        message('record cannot be locked');
        RAISE FORM_TRIGGER_FIALURE;
    END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points