1.The rows in the JOBS table store a minimum and maximum salary allowed for different JOB_ID values. You are asked to write code to ensure that employees salaries fall in the range allowed for their job type, for insert and update operations.
a.Write a procedure called CHECK_SALARY that accepts two parameters, one for an employees job ID string and the other for the salary. The procedure uses the job ID to determine the minimum and maximum salary for the specified job. If the salary parameter does not fall within the salary range of the job, inclusive of the minimum and maximum, then it should raise an application exception, with the message Invalid salary < sal>. Salaries for job < jobid> must be between < min> and < max>.
Test the procedure using the execute command. For example:
execute check_salary('ST_MAN', 2000);
execute check_salary('ST_MAN', 6000);
b.Create a trigger called CHECK_SALARY_TRG on the EMPLOYEES table that fires before an INSERT or UPDATE operation on each row. The trigger must call the CHECK_SALARY procedure to carry out the business logic. The trigger should pass the new job ID and salary to the procedure parameters.
2.Test the CHECK_SALARY_TRG
a.Add employee Eleanor Beh to department 30. What happens and explains why?
b.Update the salary of employee 115 to $2000. In a separate update operation, change the employee job ID to HR_REP. What happens in each case?
c.Update the salary of employee 115 to $2800. What happens?