error on when dup_val_on_index then.......
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: error on when dup_val_on_index then.......

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    error on when dup_val_on_index then.......

    Hi,
    I'd like to change message:
    ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

    if I create a procedure:
    begin
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
    10, 'aaaa', 'bbbbb');
    exception
    when dup_val_on_index then
    raise_application_error( -20100, 'change your value please!' );
    end;

    it run correctly.

    But I'd like to create a trigger that fire when I insert a new record.

    I tried this:
    CREATE OR REPLACE TRIGGER dept_test
    after INSERT ON dept
    FOR EACH ROW
    BEGIN
    if inserting then
    null;
    end if;
    exception
    when dup_val_on_index then
    raise_application_error( -20100, 'cambia il tuo valore, esiste' );
    END dept_TEST;

    but It doesn't run

    How can I create this trigger??

    Thanks
    Raf

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: error on when dup_val_on_index then.......

    Originally posted by raf

    But I'd like to create a trigger that fire when I insert a new record.

    I tried this:
    CREATE OR REPLACE TRIGGER dept_test
    after INSERT ON dept
    FOR EACH ROW
    BEGIN
    if inserting then
    null;
    end if;
    exception
    when dup_val_on_index then
    raise_application_error( -20100, 'cambia il tuo valore, esiste' );
    END dept_TEST;

    but It doesn't run

    How can I create this trigger??

    Thanks
    Raf
    You con't -- an AFTER trigger is not going to fire until the row is inserted, which it never is because the error gets raised.

    A BEFORE trigger probably wouldn't work because it would have to read the table to detect whether the new value is already there, and you would get a mutating table error. It'd also be extremely inefficient, even if it did work.

    So, why do you want to do this?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    This type of check should be done on the "front-end" application, not after the fact in the database. SQLPlus handles it ok, but not many users will like using SQLPlus and their interface

    Gregg

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