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.

  1. In real life scenarios, BEFORE INSERT Trigger mostly used for purposes like, Restrict Invalid Data Entry in DB.
  2. Insert/Update some value before insertion of the data (e.g CREATED_BY, CREATION_DATE etc).
Restrictions on BEFORE INSERT Trigger:
  1.  We can not create this trigger on VIEW.
  2.  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
Output:
table EMPLOYEE_DETAILS created.
Output
TRIGGER TRG_BEFORE_EMP_INSR compiled
Output
1 rows inserted.
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.
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

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
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
Output
table BANK_TRANSACTIONS created.
Output
TRIGGER TRG_BEFORE_TXN_INSR_TXNINFO compiled
TXN_ID TXN_NUMBER PARTY_NAME TXN_DATE CREATED_BY CREATED_DATE
1 TXN1234 Peter Thomas 24-MAY-2017 HR 24-MAY-2017
2 TXN9999 Jemes Patel 24-MAY-2017 HR 24-MAY-2017
References
  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *