Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: RAISE_APPLICATION_ERROR problem

    Hi,

    I want to make a trigger that validates inserts and updates in a table. Now, for example, if a column "email" has no '@' in it, the trigger should report an error.

    But I also want to show a log of all user errors so I can keep a track.

    This is how I did it:

    CREATE SEQUENCE counter
    INCREMENT BY 1
    START WITH 1
    NOCACHE
    NOCYCLE;

    CREATE TABLE log_errors(id number PRIMARY KEY,
    table_name varchar2(30) NOT NULL,
    operation varchar2(30) NOT NULL,
    column_name varchar2(30) NOT NULL,
    row_name varchar2(30) NOT NULL,
    error_log varchar2(30) NOT NULL,
    user_error varchar2(30) NOT NULL,
    date_error date NOT NULL);

    CREATE OR REPLACE TRIGGER validation_table
    AFTER INSERT ON table_name
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    begin
    if :new.email NOT LIKE '%@%' then
    INSERT INTO log_errors VALUES(
    counter.nextval,
    'Table',
    'Insert',
    'email',
    :new.email,
    'Adress has no @',
    user,
    sysdate);
    RAISE_APPLICATION_ERROR (-20500, ''Adress has no @.');
    end if;
    end;


    The problem is, when I insert a row with bad email, the RAISE_APPLICATION_ERROR error log shows, but the insert into log table never hapens. Why? Does anyone here have a solution? Thanks in advance.
    Last edited by kamion; 06-02-10 at 13:18.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does anyone here have a solution?
    Just curious, when does COMMIT occur?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The failure in the trigger rollback all the insert including the one in the trigger. Make a standalone procedure that uses Autonomous transaction. Then call the procedure from the trigger. This will allow you to do the logging and also allow the trigger to rollback. Use Google to find out what an Autonomous transaction is.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2010
    Posts
    2
    Quote Originally Posted by beilstwh View Post
    The failure in the trigger rollback all the insert including the one in the trigger. Make a standalone procedure that uses Autonomous transaction. Then call the procedure from the trigger. This will allow you to do the logging and also allow the trigger to rollback. Use Google to find out what an Autonomous transaction is.
    Thanks a lot, it works now!

Posting Permissions

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