Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: Exception in trigger and continue?

    Hi,

    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?

    regards,
    Jaap


    Trigger on MAINTABLE:

    DECLARE
    e_errmess VARCHAR2(100);

    BEGIN
    BEGIN
    INSERT INTO SHADOWTABLE
    (
    field1,
    field2
    )
    VALUES (
    :new.field1,
    :new.field2
    );
    END;

    EXCEPTION
    WHEN OTHERS THEN -- catch all errors
    BEGIN
    e_errmess := SUBSTR(SQLERRM,1,100);
    INSERT INTO LOGTABLE
    VALUES (
    SYSDATE,
    e_errmess,
    );
    END;
    END;

  2. #2
    Join Date
    Dec 2003
    Posts
    5

    Re: Exception in trigger and continue?

    I have the same setup as above as well, but here I need

    If anything goes wrong with the (insert in the) trigger I need:
    1) the record NOT be inserted into MAINTABLE
    2) a record inserted into a LOGFILE

    Anybody knows how to do this?

    Regards,
    Jaap

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Write an AFTER INSERT TRIGGER instead of BEFORE INSERT

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    u r changing your requirement!!!
    NEver mind,
    use RAISE_APPICATION_ERROR function to raise an error
    this will rollback the operation!!

  5. #5
    Join Date
    Dec 2003
    Posts
    5

    Thumbs up

    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.

    Jaap

    Originally posted by shelva
    u r changing your requirement!!!
    NEver mind,
    use RAISE_APPICATION_ERROR function to raise an error
    this will rollback the operation!!

  6. #6
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hmm
    Tricky situation.

    Firstly, When the insert fails, the before insert trigger will never fire.

    so any exception u put in the trigger will never be executed.

    i suggest, you handle this exception in the application/ script which is inserting the data into the Main table.

    Hope i am clear....

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    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):

    sqlplus 'system/manager'
    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:
    connect scott/tiger
    CREATE OR REPLACE PROCEDURE writelog_ME(
    e_triggername in varchar2,
    e_errcode in number,
    e_errmess in varchar2)
    AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_output_filehandler utl_file.file_type;
    BEGIN
    --- insert logmessage into logtable
    insert into LOGTABLE
    values(
    SYSDATE,
    e_triggername,
    e_errcode,
    e_errmess
    );
    commit;

    --- write logmessage into logfile
    v_output_filehandler := utl_file.fopen('LOG_DIR', 'Error.log', 'a');
    utl_file.put_line(v_output_filehandler, 'Trigger '
    ||e_triggername || '; error: '|| e_errcode
    || ': '|| e_errmess || '. Keys/value: '
    || e_keyname1||'/'||e_keyvalue1||'.');
    utl_file.fclose(v_output_filehandler);
    EXCEPTION
    when others
    then
    rollback;
    END;


    From within sqlplus this can be checked with:
    execute writelog_ME('NAME', '15', 'MESS', 'KEY1', 'VAL1');

    In the exception block of the trigger:
    BEGIN
    writelog_ME('NAME', '15', 'MESS', 'KEY1', 'VAL1');
    END;

    Now even if the 'main' transaction is rolled back, this procedure will be called and the log will be written to logtable and written to /tmp/Error.log

    Thanx for your help
    Jaap


    Originally posted by shelva
    Hmm
    Tricky situation.

    Firstly, When the insert fails, the before insert trigger will never fire.

    so any exception u put in the trigger will never be executed.

    i suggest, you handle this exception in the application/ script which is inserting the data into the Main table.

    Hope i am clear....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •