How to insert data into table even if trigger fails?

Question

Oracle 11.1

I have custom logging table where I insert data:

CREATE TABLE log_table
(
    message      VARCHAR2(255),
    created_by   VARCHAR2(40) NOT NULL,
    created_at   DATE         NOT NULL,
);

I have a trigger that runs on a specific table which does some checkings. My problem is: when the trigger fails, I want to be able to log some data into the log_table.

Trigger:

CREATE OR REPLACE TRIGGER my_trigger
    FOR INSERT OR UPDATE OF column
    ON my_table
    COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
   // code
END BEFORE STATEMENT;

    BEFORE EACH ROW IS
    BEGIN
        IF (/*condition for failing*/) THEN
            EXECUTE IMMEDIATE 'INSERT INTO mesaj_ama VALUES (:my_message, :my_user, :my_data)'
                USING 'custom error message', SYS.LOGIN_USER, SYSDATE;
            RAISE_APPLICATION_ERROR(-20001, 'some error');
        END IF;
    END BEFORE EACH ROW;
    END my_trigger;
/

The following code doesn't work. I tried to use EXECUTE IMMEDIATE maybe to force it, but didn't work. I know that in case of an error, there is automatically a table rollback (which means that the INSERT command is cancelled), but I need a way to do this. Any help?

Answer 1

Yes, PRAGMA AUTONOMOUS_TRANSACTION seems to be the answer. Here is the working code:

Defined a procedure for logging:

CREATE OR REPLACE PROCEDURE log_error(p_error log_table.message % TYPE) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO log_table
    VALUES (p_error, SYS.LOGIN_USER, SYSDATE);
    COMMIT;
END;

and the trigger which calls the procedure:

CREATE OR REPLACE TRIGGER my_trigger
    FOR INSERT OR UPDATE OF column
    ON my_table
    COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
   // code
END BEFORE STATEMENT;

    BEFORE EACH ROW IS
    BEGIN
        IF (/*condition for failing*/) THEN

           log_error('custom error message');
           RAISE_APPLICATION_ERROR(-20001, 'custom error message');

        END IF;
    END BEFORE EACH ROW;
    END my_trigger;
/

Answer 2

The concept you're looking for is an Autonomous Trasnaction, eg

CREATE OR REPLACE TRIGGER log_sal
  BEFORE UPDATE OF salary ON emp FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log (
    log_id,
    up_date,
    new_sal,
    old_sal
  )
  VALUES (
    :old.employee_id,
    SYSDATE,
    :new.salary,
    :old.salary
  );
  COMMIT;
END;