I have 2 tables, MAINTABLE and SHADOWTABLE.
They have the same structure.
I place a trigger (before each row, insert) on MAINTABLE that will insert the record into the SHADOWTABLE as well.
I insert a record into MAINTABLE.
If anything goes wrong with the (insert in the) trigger I need:
1) the record be inserted into MAINTABLE
2) a record inserted into a LOGFILE
When the insert statement in the trigger goes wrong an exception is raised. In the exception block I want to do the insert into the LOGTABLE. But because the trigger raised an exception the whole transaction is cancelled, the MAINTABLE is never updated and the insert into the LOGFILE is never committed...
How can I get Oracle to do what I want?
Trigger on MAINTABLE:
INSERT INTO SHADOWTABLE
WHEN OTHERS THEN -- catch all errors
e_errmess := SUBSTR(SQLERRM,1,100);
INSERT INTO LOGTABLE
Sorry shelva, I didn't actually change the requirement but added a second situation that I forgot to include, I was not too clear on that.
Thanx for the reply, I'ts pretty clear
Problem is however that when I raise an error using RAISE_APPICATION_ERROR the whole transaction is cancelled (like I need) BUT: the insert into the LOGTABLE that is located in the exception block is not committed either...
So what I'm looking for is a way to cancel the 'main' transaction but at the same time perform an insert statement into the LOGTABEL.
Originally posted by shelva
u r changing your requirement!!!
use RAISE_APPICATION_ERROR function to raise an error
this will rollback the operation!!
Unfortunately I have to create an interface between 2 applications that don't handle the errors very well, and I can't change the behaviour of those programs.
Found a solution anyway which (as usual) is pretty easy...
From Oracle8i on you can use the autonomous transaction.
I want to log to logtable and logfile on disk, so I created a procedure (logged in as system):
CREATE DIRECTORY LOG_DIR AS '/tmp';
GRANT READ ON DIRECTORY LOG_DIR TO PUBLIC;
(to check available directories)
column OWNER format a15;
column DIRECTORY_NAME format a15;
column DIRECTORY_PATH format a25;
select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories;
Now create the procedure (logged in) as normal user:
CREATE OR REPLACE PROCEDURE writelog_ME(
e_triggername in varchar2,
e_errcode in number,
e_errmess in varchar2)
--- insert logmessage into logtable
insert into LOGTABLE