Trigger to restrict salary within 1000
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;
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: