1 Reply Latest reply on Dec 9, 2009 8:30 PM by Andreas Weiden

    [Block based on From Query] On-Lock Trigger

    JeanParis
      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
          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;