Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unhappy Unanswered: Trigger to use RAISERROR

    Hello, I am trying to create a trigger that will display a custom error message. Unfortunately, I can not get it to compile without errors. Here's my code:

    create or replace trigger TEST
    before insert or update on Table1
    for each row
    begin
    raiserror(9999,16,-1,'Record Updated')
    end

    Can someone help me with this task?

    Thank you so kindly

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Do you want the value updated/inserted or not??

    PHP Code:
    RAISE_APPLICATION_ERROR (9999'Record Updated'); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2004
    Posts
    15
    I actually just want to display a custom message on the screen. The 'updated' is just in there for test purposes.

    I tried the code you supplied however it errored out on me.

    ORA-21000: error number argument to raise_application_error of 9999 is out of range

    What I would like to have ultimately happen, is after the record is added/updated, I want a message box to display on the screen telling the person that the record was updated.

    In VisualBasic, I would program as such MsgBox ('Updated')

    However, Oracle/SQL is of course entirely different.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    well for correctness:
    PHP Code:
    RAISE_APPLICATION_ERROR (-20001'Record Updated'); 
    anyways, this would CANCEL the update which is not what you want.

    you would either have an OUT parameter stating 'updated'
    or I believe through error handling you can do something.

    also, I would assume if you get no error then the default could be:
    'updated'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What you would have to do is to code this insert/update success logic on your front end (forms or reports for example), rather than your back end (the database), having an anonymous block to control its success and inside of that block the exception handler block to control its failness.

Posting Permissions

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