SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
no rows selected
SQL>
SQL> create or replace trigger emp_biu
2 BEFORE INSERT OR UPDATE
3 of salary
4 on employee
5 for each row
6 declare
7 v_error VARCHAR2(2000);
8 begin
9 if :new.salary > 10000
10 then
11 v_error:=:old.first_name||' cannot have that much!';
12 raise_application_error(-20999,v_error);
13 end if;
14
15
16 end;
17 /
Trigger created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 11232.78,'Vancouver', 'Tester')
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1:
ORA-20999: cannot have that much!
ORA-06512: at "JAVA2S.EMP_BIU", line 7
ORA-04088: error during execution of trigger 'JAVA2S.EMP_BIU'
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>