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

  1. 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!


-

Popular posts from this blog

html/hta mutiple file in audio player -

ok started coding .hta application , pretty html page .hta extension , adde mp3 player , did work <object data="test.mp3" type="audio/mp3" width="200" height="40"> <param name="src" value="test.mp3"> <param name="autostart" value="0"> </object> but it's 1 song playing again , again , plz there way add multiple files playlist believe it's should added "src" parameters not sure, i'm starting did add "," in between , , tried ";" none of them working i'm waiting answers :d i wanna know if can show me how become familiar tag see it's helpful , powerful activex, audio,video,pdf .... files i'm waiting impatiently [i don't know object, plz plz more clear] get inspired example hta_audio_player.hta <html> <head> <title>hackoo audio player playlist hackoo 2014</title> <hta:app...
Read more

debugging - Reference - What does this error mean in PHP? -

what this? this number of answers warnings, errors , notices might encounter while programming php , have no clue how fix. community wiki, invited participate in adding , maintaining list. why this? questions "headers sent" or "calling member of non-object" pop on stack overflow. root cause of questions same. answers questions typically repeat them , show op line change in his/her particular case. these answers not add value site because apply op's particular code. other users having same error can not read solution out of because localized. sad, because once understood root cause, fixing error trivial. hence, list tries explain solution in general way apply. what should here? if question has been marked duplicate of this, please find error message below , apply fix code. answers contain further links investigate in case shouldn't clear general answer alone. if want contribute, please add "favorite" error message, warning or notic...
Read more