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
Re: error on when dup_val_on_index then.......
Quote:
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?
