oracle - SQL trigger email unique -
i want create trigger (and not constraint) verify if email exists already. if so, trigger raises error. if not, email inserted table.
i started isn't working:
create or replace trigger trigger3 before insert on fv_client each row begin if exists (select courriel fv_client courriel=:new.courriel) raise_application_error(-20001,'courriel deja existant, choisir une autre combinaison courriel/mot de passe'); end if; end;
due command raise_application_error guess it's oracle database. assuming have table:
create table fv_client ( courriel varchar2(48) ); you use trigger:
create or replace trigger trigger3 before insert on fv_client each row declare cursor check_curriel select 1 fv_client courriel = :new.courriel; x varchar2(48); begin open check_curriel; fetch check_curriel x; if not check_curriel%notfound close check_curriel; raise_application_error(-20001,'courriel deja existant, choisir une autre combinaison courriel/mot de passe'); end if; close check_curriel; end; first 2 insert's without error:
insert fv_client values ('mon.pere@paris.fr'); insert fv_client values ('mon.frere@lyon.fr'); third insert throws exception intended:
insert fv_client values ('mon.pere@paris.fr'); fehlerbericht - sql-fehler: ora-20001: courriel deja existant, choisir une autre combinaison courriel/mot de passe ora-06512: @ "scott.trigger3", line 11 ora-04088: error during execution of trigger 'scott.trigger3'
p.s.: per comment of justin cave using trigger above might result in mutating table exception, instance:
insert fv_client select courriel fv_client courriel = 'mon.pere@paris.fr'; returns
- 00000 - "table %s.%s mutating, trigger/function may not see it" *cause: trigger (or user defined plsql function referenced in statement) attempted @ (or modify) table in middle of being modified statement fired it. *action: rewrite trigger (or function) not read table.
so it's recommended use unique constraint. note!