SQL trigger to restrict user to update salary of accountant

Solution for SQL trigger to restrict user to update salary of accountant
is Given Below:

I have to create a trigger that restricts any user from updating the salary of employees where job_title is accountant. For example: if someone uses the below command to update a salary:

UPDATE Employees
SET Salary = 11000
WHERE job_id = '101';   

Then it first checks if the job_title associated with job_id of 101 is accountant. If yes then throw an error message that says you cannot update the salary of an accountant. Here is my trigger code but it is not working correctly.

CREATE OR REPLACE TRIGGER salaryrestruction
BEFORE UPDATE ON employees
    DECLARE job_title employees.job_title%type;
    BEGIN
    IF UPDATING('SALARY') AND JOB_TITLE='accountant'
    THEN RAISE_APPLICATION_ERROR
    (-20501,'You cannot update SALARY of accountant');
    END IF;
END;

Here is my data of table:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_TITLE                SALARY COMMISSION_PCT
----------- -------------------- ------------------------- ------------------------- -------------------- --------- -------------------- ---------- --------------
        102 Randall              Zlotkey                   [email protected]         13675464345          22-MAY-02 Manager                   20000               
        103 John                 Bernstein                 [email protected]     12876454345          23-JUN-03 Accountant                10000             .2
        104 Peter                Sully                     [email protected]           13187754345          01-FEB-03 Executive                  8000             .4
        105 Alberto              Hall                      [email protected]            10000354345          06-APR-05 Human Resources            4500               
        106 Karen                Olsen                     [email protected]           13144444345          13-MAY-05 Marketing                 10000             .1
        107 Peter                Christopher               [email protected]     13456754345          13-MAY-06 Administration            12000             .2
        101 Lex                  De Haan                   [email protected]             13124354345          19-JAN-01 Accountant                 9000             .3

Only a row-level trigger can see each individual job title being updated. Then, you can refer to it within the trigger body using :new.job_title or in the header as new.job_title.

If you use a when condition in the header, then you don’t need to check for it in the trigger body. You also don’t need to check whether the trigger is updating in a before update trigger.

You do however need to check whether the salary is being changed, if that is your business rule. (I haven’t coded for the possibility that salary was previously null or is being updated to null – if that’s possible then you’ll need a couple more lines of code.)

create or replace trigger salaryrestriction
    before update on employees for each row
    when (new.job_title="Accountant")
begin
    if :new.salary <> :old.salary then
        raise_application_error(-20501, 'You cannot update SALARY of accountant');
    end if;
end;

We don’t need to write code in uppercase, because it’s not COBOL and this isn’t 1974.