Main Tutorials

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.

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

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

References

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

About Author

author image
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Subscribe
Notify of
2 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
asdanks
6 years ago

this is what i looking for, nice share thx

Rahul
3 years ago

thank you so much for explaining the concept .