1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl/sql trigger, exception not working

Discussion in 'SQL PL/SQL' started by asfsaf, Aug 7, 2016.

  1. asfsaf

    asfsaf Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    wer
    Code (SQL):
    DECLARE pragma exception_init(my_exception,-20001);

    BEGIN

    IF x>0 THEN
    raise_application_error(.......);
    ELSE
    UPDATE ...........
    END IF;

    exception WHEN my_exception THEN
    dbms_output.put_line(sqlerrm);
    When x > 0 it will raise the application error, but still update the table row. Can i know what is the problem?
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    How can we know what value in X contains ?

    I hope we need like this.

    if NVL(X,0) > 0 then
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The first problem is this code doesn't work as y0u posted it; if you post non-working code no one can help you.

    There are several glaring errors with this 'code':

    * No exception is declared
    * There is no code that sets or reads a value for x, thus it is NULL and fails the inequality test, causing the update to proceed
    * There is no end; to the block, so it won't compile

    Post WORKING code, or you won't receive any assistance since NO ONE can see what might be wrong.