title: oracle trigger log
body: <p>I have a trigger which is supposed to test whether an individual punching a time clock has punched their card within the last 5 minutes. If they have not their info should be left to go on to the table clk_data, otherwise a record should be made in the log table utl_logbook and the insert to clk_data should be aborted.</p>
  
  <p>When I run a good punch the insert works as advertised. When it is a bad punch (within 5 minutes of the last punch) I get the following error:</p>
  
  <pre><code>SQL Error: ORA-20101: Too Soon
  ORA-06512: at "TRUTRACK.INSERT_CLK_DATA", line 14
  ORA-04088: error during execution of trigger 'TRUTRACK.INSERT_CLK_DATA'
  </code></pre>
  
  <p>The transaction is stopped but I get no recording of it in utl_logbook.</p>
  
  <pre><code>The trigger code:
  create or replace
  TRIGGER "INSERT_CLK_DATA" 
  BEFORE INSERT ON clk_data 
  FOR EACH row 
  
  BEGIN
    DECLARE qty INTEGER := 0;
    BEGIN
      SELECT COUNT(*)
      INTO qty
      FROM clk_data
      WHERE clk_time BETWEEN (:new.clk_time - 5/(24*60)) AND (:new.clk_time + 5/(24*60))
      AND :new.payroll = clk_data.payroll;
  
      IF qty &gt; 0 THEN
  
        INSERT INTO utl_logbook (time, source, message) VALUES (sysdate, 'INSERT_CLK_DATA', 'Clock punch within restricted window. Payroll ID:' || :new.payroll || ' Time: ' || :new.clk_time || ' Type: ' || :new.type);
  
        RAISE_APPLICATION_ERROR(-20101, 'Too Soon');
  
      END IF;
  
    END;
  END;
  </code></pre>