oracle trigger log

Question

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.

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:

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'

The transaction is stopped but I get no recording of it in utl_logbook.

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

Answer 1

This is can be one of those rare cases when you can employ pragma autonomous_transaction. It will allow you commit without affecting your main transaction. Use it with caution. Find out more about autonomous transactions and autonomous_transaction pragma

Here is an example:

   -- our error logging table 

   create table tb_log(
      msg varchar2(123)
    )
    /

   -- our working table
   create table tb_table(
     col11 number
    )
    /

-- procedure that is going to log errors


NK@XE> create or replace procedure log_error(p_msg in varchar2)
  2    is
  3      pragma autonomous_transaction;
  4    begin
  5      insert into tb_log(msg)
  6        values(p_msg);
  7      commit;
  8    end;
NK@XE> /

Procedure created.

NK@XE> create or replace trigger tr_tb_table
  2    before insert on tb_table
  3    for each row
  4    begin
  5      if mod(:new.col1, 2) != 0
  6      then
  7         log_error('Error!');
  8         raise_application_error(-20000, 'Error has ocurred!');
  9      end if;
 10    end;
 11  /

Trigger created.

NK@XE> select * from tb_log;

no rows selected

NK@XE> select * from tb_table;

no rows selected

NK@XE> insert into tb_table(col1) values(1);

insert into tb_table(col1) values(1)
            *
ERROR at line 1:
ORA-20000: Error has ocurred!
ORA-06512: at "NK.TR_TB_TABLE", line 5
ORA-04088: error during execution of trigger 'NK.TR_TB_TABLE'


NK@XE> select * from tb_log;

MSG
--------------------------------------------------------------------------------
Error!

Answer 2

@nicholas-krasnov provided the solution, just to elaborate a little on the reasoning - you are throwing an exception in the trigger body:

RAISE_APPLICATION_ERROR(-20101, 'Too Soon');

which results in a rollback of the transaction. Full details of this can be found:

http://psoug.org/reference/exception_handling.html

If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.