How To Insert Data Into Table Even If Trigger Fails?
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
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?
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;
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key