Ad
Oracle Trigger Befor Update And Insert Using :new And :old
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
Ad
Answer
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.
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad