exception handling in a trigger?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: exception handling in a trigger?

  1. #1
    Join Date
    Apr 2002
    Posts
    29

    exception handling in a trigger?

    can i actually do exception handling in a trigger? like in a procedure?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Yes, you can.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi
    use:
    create or replace trigger trigger_name
    before/after insert/delete/update on table_name
    (for each row)
    begin
    perform checks on data or call package/procedure/function
    exception
    when others then
    raise_application_error(-20000,'error in trigger');

    end;
    /

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  4. #4
    Join Date
    Apr 2002
    Posts
    29

    thank u for your help

    i must say this forum offers me alot of help and i really apprieciate when experienced folks are willing to help..

    thanks!

  5. #5
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    Mostly, I use

    if (....) then
    raise_application_error(xxxxx,'error msg');
    end if

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by dba1
    Mostly, I use

    if (....) then
    raise_application_error(xxxxx,'error msg');
    end if

    I am sorry but that is NOT an exception

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by dba1
    Mostly, I use

    if (....) then
    raise_application_error(xxxxx,'error msg');
    end if
    Agree with Pando. This is more like handling a case via a procedure.

    This is an example of exception handling using a user-defined exception:

    DECLARE
    v_cnt NUMBER;
    count_more_than_one EXCEPTION;
    BEGIN
    select count(*) into v_cnt
    from whatever
    where whatever;

    if v_cnt > 1 then
    raise count_more_than_one;
    else
    whatever;
    end if;
    EXCEPTION
    when count_more_than_one then
    dbms_output.put_line('Some error message you can build here or up above');

    when no_data_found then
    this is a built-in exception;
    END;

Posting Permissions

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



Click Here to Expand Forum to Full Width