PL/SQL - How to create a trigger that checks if a name follows a specific pattern: it must consist of a letter and two numbers

Question

I have a table called CREWS with a column CREW_NUMBER.

Each CREW_NUMBER must follow a 1 letter 2 number format, e.g. A07, B33, G86, etc.

How do I create a trigger that checks on insert or update whether the crew number that was just added follows this pattern and throws up an exception if it doesn't?

It would be great if the solution is not something like "take the crew number, break it into three variables symbol by symbol and check each one"

Answer 1

Check constraint (as jarlh commented) seems to be a better option. For example:

SQL> create table crews
  2    (crew_number varchar2(3) constraint ch_cn check
  3      (regexp_like(crew_number, '[[:alpha:]]{1}[[:digit:]]{2}'))
  4    );

Table created.

SQL> insert into crews values ('abc');
insert into crews values ('abc')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_CN) violated


SQL> insert into crews values ('a32');

1 row created.

SQL> insert into crews values ('3ab');
insert into crews values ('3ab')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_CN) violated


SQL> insert into crews values ('ab2');
insert into crews values ('ab2')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_CN) violated


SQL>

---

If it - as you commented - has to be a trigger, then:

SQL> create table crews
  2    (crew_number varchar2(3));

Table created.

SQL> create or replace trigger trg_biu_crew
  2    before insert or update on crews
  3    for each row
  4  begin
  5    if not regexp_like(:new.crew_number, '[[:alpha:]]{1}[[:digit:]]{2}') then
  6       raise_application_error(-20000, 'Invalid format; use one letter + 2 digits');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into crews values ('abc');
insert into crews values ('abc')
            *
ERROR at line 1:
ORA-20000: Invalid format; use one letter + 2 digits
ORA-06512: at "SCOTT.TRG_BIU_CREW", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_CREW'


SQL> insert into crews values ('a32');

1 row created.

SQL> insert into crews values ('3ab');
insert into crews values ('3ab')
            *
ERROR at line 1:
ORA-20000: Invalid format; use one letter + 2 digits
ORA-06512: at "SCOTT.TRG_BIU_CREW", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_CREW'


SQL> insert into crews values ('ab2');
insert into crews values ('ab2')
            *
ERROR at line 1:
ORA-20000: Invalid format; use one letter + 2 digits
ORA-06512: at "SCOTT.TRG_BIU_CREW", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_CREW'


SQL>