Oracle PL/SQL Before INSERT Trigger Example

Overview

This Article will help you to understand “BEFORE INSERT TRIGGER statement” with examples and it’s detailed description.

This statement specifies that Oracle will fire this trigger BEFORE the INSERT operation is executed.

In real life scenarios, BEFORE INSERT Trigger mostly used for purposes like, Restrict Invalid Data Entry in DB.
Insert/Update some value before insertion of the data (e.g CREATED_BY, CREATION_DATE etc).

Restrictions on BEFORE INSERT Trigger:

We can not create this trigger on VIEW.
We can not update :OLD value, as in INSERT it’s not present.

Examples

Validation

(1) Restricting some invalid data entry in DB.

Description

In this example we have introduced two restrictions on before insert statement. User can’t enter employees’ date of birth which doesn’t comply with the rule of 18 years age of employee. In other restriction, user can’t enter Future Date of Death. If any condition will be violated then trigger will Raise application error which will give error message and stops execution of INSERT statement.

First, we will create table employee_details.

Then we will create “trg_before_emp_insr” Trigger on table employee_details.

We will try to insert different values in this table and observe the result.

Code


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

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


-- Creating TRIGGER

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(TRUNC(sysdate), 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;
/

-- Creating TRIGGER

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(TRUNC(sysdate), 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;

Output

TRIGGER TRG_BEFORE_EMP_INSR compiled


-- setting date format to to 'DD-MON-YYYY'

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO employee_details VALUES (1,'Churchil','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate);

-- setting date format to to 'DD-MON-YYYY'

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO employee_details VALUES (1,'Churchil','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate);

Output

1 rows inserted.


INSERT INTO employee_details VALUES (2,'Mathew','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate);

INSERT INTO employee_details VALUES (2,'Mathew','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate);

Output

Error starting at line 40 in command: INSERT INTO employee_details VALUES (2,’Mathew’,’Peter’,’18-MAY-2010′,’01-MAY-2017′,’HR’,sysdate) Error report: SQL Error: ORA-20000: Employee age must be greater than or equal to 18. ORA-06512: at “HR.TRG_BEFORE_EMP_INSR”, line 11 ORA-04088: error during execution of trigger ‘HR.TRG_BEFORE_EMP_INSR’ 20000. 00000 – “%s” *Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. *Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.


INSERT INTO employee_details VALUES (3,'Brett','Avis','18-MAY-1999','01-MAY-2040','HR',sysdate);

INSERT INTO employee_details VALUES (3,'Brett','Avis','18-MAY-1999','01-MAY-2040','HR',sysdate);

Output

Error starting at line 42 in command: INSERT INTO employee_details VALUES (3,’Brett’,’Avis’,’18-MAY-1999′,’01-MAY-2040′,’HR’,sysdate) Error report: SQL Error: ORA-20000: Date of death can not be Future date. ORA-06512: at “HR.TRG_BEFORE_EMP_INSR”, line 16 ORA-04088: error during execution of trigger ‘HR.TRG_BEFORE_EMP_INSR’ 20000. 00000 – “%s” *Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. *Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.

Insert or Update values (same table)

(2) Insert/Update some value before insertion of the data (e.g created_by, created_date etc).

Description

In This example, we will insert values of few columns (here, created_by and created_date) without caring about what values given in INSERT statement. whether user enters null or any values in these fields, Trigger will update those values and then in Database, newly updated values will be inserted.

Here, we are using the same table “employee_details” which we created in previous example.

We will insert three rows in this table and observe the inserted values in DB.

In First INSERT statement, values of CREATED_BY and CREATED_DATE is null. And in second INSERT, value of CREATED_BY is “XYZ” which will be replaced by current logged in user “HR” by trigger.

We have three rows, notice the values of CREATED_BY and CREATED_DATE in other two rows.

In Tirgger, CREATED_BY and CREATED_DATE are being set without taking any care of provided values in INSERT statement.

Code


CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo
BEFORE INSERT
  ON employee_details
  FOR EACH ROW

DECLARE
username varchar2(20);

BEGIN

  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;

CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo

BEFORE INSERT

ON employee_details

FOR EACH ROW

DECLARE

username varchar2(20);

BEGIN

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;

We have one row in table as of now with below value.

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


INSERT INTO employee_details VALUES (2,'Mathew','Peter','01-JAN-1990','01-MAY-2005',null,null);

INSERT INTO employee_details VALUES (3,'Brett','Avis','01-JAN-1990','01-MAY-2005','XYZ',null);

select * from employee_details;

INSERT INTO employee_details VALUES (2,'Mathew','Peter','01-JAN-1990','01-MAY-2005',null,null);

INSERT INTO employee_details VALUES (3,'Brett','Avis','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	Churchil	Thomas	18-MAY-1999	01-MAY-2017	HR	24-MAY-2017
2	Mathew	Peter	01-JAN-1990	01-MAY-2005	HR	24-MAY-2017
3	Brett	Avis	01-JAN-1990	01-MAY-2005	HR	24-MAY-2017

Insert or Update values (different table)

(3) Insert/Update some value before insertion of the data

Description

This is similar kind of example like previous example.

Here first we will create table “bank_transactions”.

On this table, we will create BEFORE INSERT Trigger “trg_before_txn_insr_txninfo”.

Then we’ll insert values in this table and observe the data inserted into DB.

In first INSERT, user is not providing values of txn_date, created_by and created_date. But after insert, values will be provided by Tirgger.

In Second INSERT, user is providing future txn date (e.g. 10-JUN-2050), but suppose we have requirement to keep txn_Date as of sysdate. So trigger will update this value to sysdate.

Code


--Creating bank_transactions table.

CREATE TABLE bank_transactions
(
    TXN_ID number(10) primary key,
    TXN_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    TXN_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

--Creating bank_transactions table.

CREATE TABLE bank_transactions

(

TXN_ID number(10) primary key,

TXN_NUMBER varchar2(20),

PARTY_NAME varchar2(50),

TXN_DATE date,

CREATED_BY varchar2(20),

CREATED_DATE date

);

Output

table BANK_TRANSACTIONS created.


--Creating Trigger

CREATE OR REPLACE TRIGGER trg_before_txn_insr_txninfo
BEFORE INSERT
  ON bank_transactions
  FOR EACH ROW

DECLARE
username varchar2(20);

BEGIN

  SELECT USER INTO username FROM dual;
  
  -- Setting txn_date, created_by and created_date values.
  :NEW.TXN_DATE := sysdate;
  :NEW.CREATED_BY := username;
  :NEW.CREATED_DATE := sysdate;

END;
/

--Creating Trigger

CREATE OR REPLACE TRIGGER trg_before_txn_insr_txninfo

BEFORE INSERT

ON bank_transactions

FOR EACH ROW

DECLARE

username varchar2(20);

BEGIN

SELECT USER INTO username FROM dual;

-- Setting txn_date, created_by and created_date values.

:NEW.TXN_DATE := sysdate;

:NEW.CREATED_BY := username;

:NEW.CREATED_DATE := sysdate;

END;

Output

TRIGGER TRG_BEFORE_TXN_INSR_TXNINFO compiled


--Inserting values

INSERT INTO BANK_TRANSACTIONS values ('1','TXN1234','Peter Thomas',null,null,null);

INSERT INTO BANK_TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2050',null,null);

select * from BANK_TRANSACTIONS;