Oracle Trigger befor update and insert using :new and :old

December 30, 2021

I’m practicing what I learned in PL/SQL
I have a table "Client" that contains :

Client ( id, name, lastName, email, city, phone, commission , salary)

The commission should always be lower than salary

I’m asked to create an oracle trigger before insert and update to make sure that commission < salary
so what I did is the following

 Create Trigger verifySalary
 Before insert, update
 ON Client
 for each row
 begin 
    if :new.salary < :new.comm then 
        raise_application_error(-20555, "commission should be lower than salary");
    end if  
 end

I’m not sure that this is correct, because if the user didn’t update the salary and the commission or updated just one of these two columns then what’s going to be the value of :new.salary and :new.commission ?

How should I proceed ?
thank you in advance

>Solution :

Trigger code you posted is invalid. When fixed (and with applied NVL function), it looks like this:

SQL> create table client (name varchar2(10), commision number, salary number);

Table created.

SQL> create or replace trigger verifysalary
  2    before insert or update on client
  3    for each row
  4  begin
  5    if nvl(:new.salary, 0) < nvl(:new.commision, 0) then
  6       raise_application_error(-20555, 'commision should be lower than salary');
  7    end if;
  8  end;
  9  /

Trigger created.

Testing:

SQL> insert into client (name, commision, salary) values ('Little', 10, null);
insert into client (name, commision, salary) values ('Little', 10, null)
            *
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'


SQL> insert into client (name, commision, salary) values ('Little', 10, 100);

1 row created.

SQL> update client set commision = 50;

1 row updated.

SQL> update client set commision = 500;
update client set commision = 500
       *
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'


SQL> select * from client;

NAME        COMMISION     SALARY
---------- ---------- ----------
Little             50        100

SQL> update client set salary = null;
update client set salary = null
       *
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'


SQL> update client set salary = 10;
update client set salary = 10
       *
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'


SQL>

Looks OK to me.