Trigger to restrict salary within 1000

Table of Contents

Trigger Restriction on DML:

We can use Trigger to restrict data insertion into employees table.

In the below example we will write a trigger which will restrict data insertion of an employee who has salary greater than 1000.

--Trigger to restrict salary within 1000 : Example

CREATE OR REPLACE TRIGGER TRIG_RESTRICT_EMP_SAL
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
  IF :NEW.SALARY > 1000 THEN
    RAISE_APPLICATION_ERROR(-20020, 'SALARY CAN NOT BE MORE THAN 1000');
  END IF;
END;
/

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

Trigger to restrict salary within 1000 : Output

Inserting one employee data with salary more than 1000 to impose the Trigger restriction:

--Trigger to restrict salary within 1000 : Example

Insert into EMPLOYEES (EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       EMAIL,
                       PHONE_NUMBER,
                       HIRE_DATE,
                       JOB_ID,
                       SALARY,
                       COMMISSION_PCT,
                       MANAGER_ID,
                       DEPARTMENT_ID
                      ) 
values (208,
        'Jhon',
        'Smith',
        'JSMITH',
        '515.127.4560',
        to_date('18-10-03','DD-MM-RR'),
        'PU_CLERK',
        1001,
        null,
        114,
        30
       );

--Trigger to restrict salary within 1000 : Example output

Error report -
SQL Error: ORA-20020: SALARY CAN NOT BE MORE THAN 1000
ORA-06512: at "HR.TRIG_RESTRICT_EMP_SAL", line 3
ORA-04088: error during execution of trigger 'HR.TRIG_RESTRICT_EMP_SAL'

In the above example oracle has restricted the insertion of a new employee whos salary is greater than 1000.

Even if the amount is entered as 1001 then also oracle has restricted the insertion.

Inserting one employee data with salary less than or equal to1000:

--Trigger to restrict salary within 1000 : Example

Insert into EMPLOYEES (EMPLOYEE_ID,
                       FIRST_NAME,
                       LAST_NAME,
                       EMAIL,
                       PHONE_NUMBER,
                       HIRE_DATE,
                       JOB_ID,
                       SALARY,
                       COMMISSION_PCT,
                       MANAGER_ID,
                       DEPARTMENT_ID
                      ) 
values (208,
        'Jhon',
        'Smith',
        'JSMITH',
        '515.127.4560',
        to_date('18-10-03','DD-MM-RR'),
        'PU_CLERK',
        1000,
        null,
        114,
        30
       );

1 row inserted.

Updating one employee data with salary more than 1000 to impose the Trigger restriction:

UPDATE EMPLOYEES
SET SALARY = 1001
WHERE EMPLOYEE_ID = 208;

--Trigger to restrict salary within 1000 : Example output

UPDATE EMPLOYEES
SET SALARY = 1001
WHERE EMPLOYEE_ID = 208
Error report -
SQL Error: ORA-20020: SALARY CAN NOT BE MORE THAN 1000
ORA-06512: at "HR.TRIG_RESTRICT_EMP_SAL", line 3
ORA-04088: error during execution of trigger 'HR.TRIG_RESTRICT_EMP_SAL'

In the above example, Oracle has restricted the updation.

Updating one employee data with salary less than or equal to 1000:

UPDATE EMPLOYEES
SET SALARY = 1000
WHERE EMPLOYEE_ID = 208;

1 row updated.

Another example:

Calling a procedure inside a trigger to raise the exception:

We can add a procedure in the declaration part of the trigger.

Then call it inside the body part of the trigger to execute the validation.

--Trigger to restrict salary within 1000 : Example

CREATE OR REPLACE TRIGGER TRIG_RESTRICT_EMP_SAL
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
DECLARE
  PROCEDURE SP_CHECK_SALARY(P_SALARY NUMBER)
  IS
    SALARY_EXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(SALARY_EXCEPTION, -20020);
  BEGIN
    IF P_SALARY > 1000 THEN
      RAISE SALARY_EXCEPTION;
    END IF;
  EXCEPTION
    WHEN SALARY_EXCEPTION THEN
      DBMS_OUTPUT.PUT_LINE('SALARY CAN NOT BE MORE THAN 1000');
      RAISE;
  END;
BEGIN
  IF :NEW.SALARY > 1000 THEN
    SP_CHECK_SALARY(:NEW.SALARY);
  END IF;
END;
/

SET SERVEROUTPUT ON;

UPDATE EMPLOYEES
SET SALARY = 1001
WHERE EMPLOYEE_ID = 208;

--Trigger to restrict salary within 1000 : Example output

Error starting at line : 20 in command -
UPDATE EMPLOYEES
SET SALARY = 1001
WHERE EMPLOYEE_ID = 208
Error report -
SQL Error: ORA-20020: 
ORA-06512: at "HR.TRIG_RESTRICT_EMP_SAL", line 13
ORA-06512: at "HR.TRIG_RESTRICT_EMP_SAL", line 8
ORA-06512: at "HR.TRIG_RESTRICT_EMP_SAL", line 17
ORA-04088: error during execution of trigger 'HR.TRIG_RESTRICT_EMP_SAL'

SALARY CAN NOT BE MORE THAN 1000

RELATED TOPICS:

sayan_bappa@yahoo.co.in

View all posts

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Trigger to restrict salary within 1000