Error
Description: Error Cause: Action:
Example case: Here in the student table have an insert trigger. The trigger fire when anything insert in student table and copy inserted information to other schema. When Insert Trigger is Enable: SQL> Insert into STUDENT(name,roll,department) values('Tom',12123, 'Business'); Insert into STUDENT * ERROR at line 1: ORA-02055: distributed update operation failed; rollback required ORA-02291: integrity constraint (FK_STUDENT_DEPARTMENT_NAME) violated - parent key not found
Now any query will give the ORA-02067 error
SQL> select sysdate from dual; select sysdate from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-02067: transaction or savepoint rollback required ORA-02067: transaction or savepoint rollback required
SQL> rollback; Rollback complete.
Now everything will be works fine again.
SQL> select sysdate from dual;
SYSDATE ------------------- 2013-12-20 11:28:43
When Insert Trigger is Disable: SQL> Insert into STUDENT(name,roll,department) values('Tom',12123, 'Business'); ERROR at line 1: ORA-02291: integrity constraint (FK_STUDENT_DEPARTMENT_NAME) violated - parent key not found
No ORA-02067 error SQL> select sysdate from dual;
SYSDATE ------------------- 2013-12-20 13:00:47
In the trigger we could catch exception for ORA-02055 and rollback but the problem is a trigger cannot contain a commit or rollback statement, however by using autonomous transaction we can overcome this limitation but it’s consider bad practice but it is possible. |