Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    You can't. Since the insert fails, the AFTER trigger is never fired. Your first method is the way to go - in the real world, users' don't insert records via SQL Plus anyway, they use an application, and the application should take care of user interface issues.

Posting Permissions

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