Validation Trigger - Exercise

Contact Us or call 1-877-932-8228
Validation Trigger - Exercise

Validation Trigger

Duration: 10 to 15 minutes.
  1. Create a trigger to raise an error if an employee salary being entered is greater than 25000.
  2. Name the trigger TR_VALIDATE_SALARY.
  3. The trigger should reference the SALARY field on the EMPLOYEES table.
  4. Test the trigger by verifying that you can set the salary of employee_id 100 to an acceptable value but that an error occurs if the salary is above 25000.
  5. Drop the trigger when you have finished testing.

Solution:

Database-Triggers/Solutions/solution-1.sql
create or replace trigger tr_validate_salary
before insert or update of 
salary on employees
for each row
begin
  
  IF :new.salary > 25000 THEN
    raise_application_error(-20001, 
                            'Invalid Salary: ' || :new.salary);
  END IF;
  
end;
/

-- Watch it fail
update employees
set salary=28000
where employee_id=100;

-- Watch it succeed
update employees
set salary=21000
where employee_id=100;

-- rollback the change before executing the DDL
rollback;

-- drop the trigger
drop trigger tr_validate_salary;

Code Explanation

Next