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?