Oracle 11.1
I have custom logging table where I insert data:
x
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 TRIGGERBEFORE STATEMENT ISBEGIN // codeEND 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?
Advertisement
Answer
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 TRIGGERBEFORE STATEMENT ISBEGIN // codeEND 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;/