Oracle PL/SQL Before INSERT Trigger example

This article shows you how to use BEFORE INSERT TRIGGER, it’s fire BEFORE an INSERT operation is executed. In real life scenarios, it is mostly used for purposes like

1. Data validation
2. Update values automatically (e.g CREATED_BY, CREATION_DATE etc)

1. Table

Create a employee_details, we will try to insert different values into this table and observe the trigger behavior.

--Creating employee_details table.
CREATE TABLE employee_details
    EMP_ID number(10) primary key,
    FIRST_NAME varchar2(50),
    LAST_NAME varchar2(50),
    DATE_OF_BIRTH date,
    DATE_OF_DEATH date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

Output

table EMPLOYEE_DETAILS created.

2. Data Validation

2.1 am before insert trigger example to restrict invalid data entry:

1. User can’t enter the employees’ date of birth, which doesn’t comply with the rule of 18 years age of the employee.
2. User can’t enter future date of death.

2.2 Create a trg_before_emp_insr trigger on table employee_details

CREATE OR REPLACE TRIGGER trg_before_emp_insr
BEFORE INSERT
  on employee_details
  FOR EACH ROW 
DECLARE
emp_age number;
BEGIN
-- Finding employee age by date of birth
SELECT MONTHS_BETWEEN(TO_DATE(sysdate,'DD-MON-YYYY'), TO_DATE(:new.DATE_OF_BIRTH,'DD-MON-YYYY'))/12 
   INTO EMP_AGE FROM DUAL;
   -- Check whether employee age is greater than 18 or not
    IF (EMP_AGE < 18) THEN
      RAISE_APPLICATION_ERROR(-20000,'Employee age must be greater than or equal to 18.');
    END IF;
    -- Allow only past date of death
    IF(:new.DATE_OF_DEATH > sysdate) THEN
      RAISE_APPLICATION_ERROR(-20000,'Date of death can not be Future date.');
    END IF;          
END;

2.3 Normal data.

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO employee_details VALUES (1,'Patel','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate);
-- output
1 rows inserted.

2.4 Test trigger raise error – Employee age must be greater than or equal to 18.

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO employee_details VALUES (2,'Patel','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate);
-- error
Error report -
ORA-20000: Employee age must be greater than or equal to 18.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_INSR", line 18
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_INSR'

2.5 Test trigger raise error – Date of death can not be Future date.

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO employee_details VALUES (3,'Patel','Thomas','18-MAY-1999','01-MAY-2040','HR',sysdate);
-- error 
Error report -
ORA-20000: Date of death can not be Future date.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_INSR", line 23
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_INSR'

3. Update Values

A before insert trigger example to update some values automatically.

CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo
BEFORE INSERT
  ON employee_details
  FOR EACH ROW
DECLARE
username varchar2(20);
BEGIN
  -- Replaced by the current logged in user "HR" by a trigger.
  SELECT USER INTO username FROM dual;
  -- Setting created_by and created_Date values.
  :NEW.CREATED_BY := username;
  :NEW.CREATED_DATE := sysdate;
END;

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
select * from employee_details;

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';
INSERT INTO employee_details VALUES (2,'Patel','Methew','01-JAN-1990','01-MAY-2005',null,null);
INSERT INTO employee_details VALUES (3,'Patel','Methew','01-JAN-1990','01-MAY-2005','XYZ',null);
select * from employee_details;

References

1. Syntax :- Oracle official docs
2. PL/SQL Triggers :- Oracle official docs