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

[raf]

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

[slimdave]

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?

[gbrabham]

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