Trigger in PLSQL

Question

why am I getting this error?

Question) write a trigger to restrict the insertion of commission more than salary.

prog,

SET SERVEROUTPUT ON;
CREATE OR REPLACE TRIGGER EMP2_TR
BEFORE INSERT ON EMP2
FOR EACH ROW WHEN (NEW.COMM > NEW.SAL)
BEGIN
    Raise_Application_Error (-20100, 'COMMISION MUST NOT BE GREATER THEN SALARY');      
END;
/

error,

SQL> INSERT INTO EMP2 VALUES(101,'RAMAN',100,1000);
INSERT INTO EMP2 VALUES(101,'RAMAN',100,1000)
            *
ERROR at line 1:
ORA-20100: COMMISION MUST NOT BE GREATER THEN SALARY
ORA-06512: at "AYUSH.EMP2_TR", line 2
ORA-04088: error during execution of trigger 'AYUSH.EMP2_TR'

table,

SQL> DESC EMP2    
 Name                                      Null?    Type    
 ----------------------------------------- -------- -------------------------    
 EMP_NO                                             NUMBER(3)    
 NAME                                               VARCHAR2(10)    
 SAL                                                NUMBER(10,3)    
 COMM                                               NUMBER(10,2)

Answer 1

Your insert does not specify the order of the values, so the order of the columns in the table is used. Most probably the sal column is defined before the comm column.

You need to list the columns in the insert statement:

insert into emp2 
  (empid, name, comm, sal) 
values 
  (101,'RAMAN',100,1000);

so that Oracle knows which value belongs to which column.

This is good coding practice anyway

Answer 2

The question is: Why are you doing this with a trigger?

You should be doing this with a constraint:

alter table emp2 add constraint chk_emp2_comm_sal check (comm <= sal);