Trigger

[filipe26]

I want to create a trigger that verifies that a email is in correct state like something@something.something

 

[SantaMufasa]

Filipe,

Absent additional specifications, here is a trigger that throws errors if an email address does not match your specifications:

create or replace trigger filipe_email_check
    before insert on filipe
    for each row
begin
    if instr(:new.email_id,'@') = 0 then
        raise_application_error (-20000,'Missing "@" in email address, "'||
            :new.email_id||'".');
    end if;
    if instr(:new.email_id,'.') = 0 then
        raise_application_error (-20001,'Missing "." in email address, "'||
            :new.email_id||'".');
    end if;
end;
/

SQL> insert into filipe values ('firstname.lastname@domain.com');

1 row created.

SQL> insert into filipe values ('[URL unfurl="true"]www.tek-tips.com');[/URL]
insert into filipe values ('[URL unfurl="true"]www.tek-tips.com')[/URL]
            *
ERROR at line 1:
ORA-20000: Missing "@" in email address, [URL unfurl="true"]www.tek-tips.com.[/URL]
ORA-06512: at "TEST.FILIPE_EMAIL_CHECK", line 3
ORA-04088: error during execution of trigger 'TEST.FILIPE_EMAIL_CHECK'

SQL> insert into filipe values ('email@domain');
insert into filipe values ('email@domain')
            *
ERROR at line 1:
ORA-20001: Missing "." in email address, "email@domain".
ORA-06512: at "TEST.FILIPE_EMAIL_CHECK", line 7
ORA-04088: error during execution of trigger 'TEST.FILIPE_EMAIL_CHECK'

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[filipe26]

yes thanks