Check new value in before insert trigger : Before Event Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Before Event Trigger

SQL>
SQL> CREATE TABLE employee (
  2  id                             number,
  3  name                           varchar(100),
  4  birth_date                     date,
  5  gender                         varchar2(30) );

Table created.

SQL>
SQL> CREATE TRIGGER   employee_bir
  2  BEFORE INSERT ON employee
  3  FOR EACH ROW
  4  begin
  5    if upper(:new.name) = 'J' then
  6      raise_application_error(20000, 'Sorry, that genius is not allowed.');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL>
SQL> INSERT INTO employee (id,name,birth_date,gender )VALUES (100,'J',to_date('19230823', 'YYYYMMDD'),'MALE' );
INSERT INTO employee (id,name,birth_date,gender )VALUES (100,'J',to_date('19230823', 'YYYYMMDD'),'MALE' )
            *
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of 20000
is out of range
ORA-06512: at "JAVA2S.EMPLOYEE_BIR", line 3
ORA-04088: error during execution of trigger 'JAVA2S.EMPLOYEE_BIR'


SQL>
SQL> drop table employee;

Table dropped.