Oracle PL/SQL – Before INSERT Trigger example

This article shows you how to use BEFORE INSERT TRIGGERBEFORE INSERT trigger 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.

employee_details

--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

trg_before_emp_insr

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 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 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.

trg_before_emp_insr_userinfo

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;

EMP_ID	FIRST_NAME	LAST_NAME	DATE_OF_BIRTH	DATE_OF_DEATH	CREATED_BY	CREATED_DATE
1	Patel	Thomas	18-MAY-1999	01-MAY-2017	HR	24-MAY-2017

-- 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;

EMP_ID	FIRST_NAME	LAST_NAME	DATE_OF_BIRTH	DATE_OF_DEATH	CREATED_BY	CREATED_DATE
1	Patel	Thomas	18-MAY-1999	01-MAY-2017	HR	24-MAY-2017
2	Patel	Methew	01-JAN-1990	01-MAY-2005	HR	24-MAY-2017
3	Patel	Methew	01-JAN-1990	01-MAY-2005	HR	24-MAY-2017

SHARES

Share

Pin it

Tweet

Share

Share

Share

Buffer

Print

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.