sql - How to improve the following stored procedure to verify the syntax of an email? -

i created following table:

create table tbl_email (     id int identity primary key,     email varchar(25) not null  ); 

i create stored procedure check if syntax of new email ok, want cover 2 possibilities if email has not "@" character follows:

insert tbl_email (email) values ('email.com')  

and if email has not corresponding dot:

 insert tbl_email (email)  values ('email@com')  

i want cover these cases, create stored procedure activated when there insert or update

create trigger tr_verify_email on tbl_email insert,update begin 

declaring variables count inside while , flag know if "@" located @ first position,

    declare @v_count int,@flag int     declare @v_email_inserted varchar(25)     set @v_count = 1     set @flag = 0     select @v_email_inserted = email inserted;      while(@v_count < len(@v_email_inserted)) begin         if(substring(@v_email_inserted,@v_count,1)='@' , @v_count > 4) begin             set @flag = @flag + 1         end         set @v_count = @v_count + 1      end 

to rollback transaction when email has , "@" @ first position,

    if(@flag <> 1) begin         raiserror('invalid email',16,1)         rollback transaction     end      end 

however first approach understand there lot of missing cases , invalid emails, appreciate suggestions improve logic , allow emails appropriated structure mean user@something.com, support.

don't use trigger. correct way check constraint:

alter table tbl_email     add constraint chk_email_email         check (email '%@%.%'); 

this ensure data integrity both inserts and updates.